Making vlookup persistent

Hello,

I've been searching for a way to make the vlookup function persistent in a cell, or at least find an alternative!

What I am trying to do is make it so that when I enter a value in A1, then B1 automatically references a table and enters a corresponding value. I can do this using vlookup, however once it has found the value, my formula disappears.

I am thinking that a macro could be used to make the cell update the information as needed, but I am not sure how I would program it. Any help would be appreciated!

I am not sure I can help you to much with the VBA part, if required, but I don't understand this part:

I can do this using vlookup, however once it has found the value, my formula disappears.

How is the formula disappearing? Is there already a macro that replaces the cell content? A formula can't disappear after it retrieves a value, unless forced by deletion or via macro.

Have you turned off auto calculations? when you enter or change to a new lookup value the result cell should change automatically

Go into Options, and look for Workbook Calculations and make sure it's set to Automatic and not manual

Originally Posted by scottylad2

Have you turned off auto calculations? when you enter or change to a new lookup value the result cell should change automatically

Go into Options, and look for Workbook Calculations and make sure it's set to Automatic and not manual

Okay, I will check that out...

This is excel 2002, incidentally, so it may not have the functionality I need I suppose...

EDIT: okay, it is set to automatic...

The thing is, if I enter a new vlookup into the cell, it works as intended.

Example...

Say I have a chart...
Date Stock# Year Make Model Color
10/24/11 J3491 2000 Chevrolet Blazer Red
10/24/11 J3492 2001 Pontiac Grand Am Gold
10/24/11 J3493 2001 Buick Century White
10/24/11 J3494 1999 Pontiac Grand Prix Silver
10/27/11 J3495 2003 Mercury Sable Wagon Red
10/27/11 J3496 2003 Dodge Durango Grey
10/27/11 J3497 2000 Dodge Grand Caravan Gold
10/27/11 J3498 2000 Ford Explorer Green
10/27/11 J3499 2002 Buick Regal Silver
10/27/11 J3500 2002 Oldsmobil Alero Red
11/03/11 J3501 1999 Ford Escort ZX2 Green
11/03/11 J3502 1999 Plymouth Breeze Tan
11/03/11 J3503 2002 Pontiac Grand Prix Blue
11/03/11 J3504 1998 Saturn SL1 Green
11/03/11 J3505 2000 Ford Taurus Green
11/03/11 J3506 1999 Buick Park Avenue Beige

(the spreadsheet doesn't copy great, but you get the idea...)

On a separate worksheet, I want to be able to put in a stock number in A1, and have B1:E1 automatically spit out the year, make, model, and color.

Currently, the only way it works is if I manually type in a vlookup in B1, C1, etc. to pull out the information that I want. It is slow, and I don't want to have to retype the formula each time. If I change the stock number from J3500 to J3501, then I have to redo them all over again.

It's in the Tools menu... I am pretty sure it's in 2002 too.

You should enter the Stock# in a cell and reference that cell in your VLOOKUPs

e.g.

=VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:B$1),FALSE)

copied across and/or down if you got a list in column A.

Where A1 contains the first Stock # to look up in a sheet called ChartSheet... adjust as necessary.

I said and/or copy down.. you don't have to copy down.

The A1 doesn't have to be in the same row..

Say you input the stock # in A1

you can retrieve the info in say D2 with:

=VLOOKUP($A$1,'ChartSheet'!$B:$E,COLUMNS($A$1:B$1),FALSE)

copied across

or just

=VLOOKUP($A$1,'ChartSheet'!$B:$E,2,FALSE)

and change the 2 to 3 to get next column, etc...

Can you upload a sample?

say you put this formula in A2 and drag along to E2, whatever data you have stored in your table this should pull through the coresponding row of expected return based on the stock number you enter in A1

=VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:A1),FALSE)

Are you expecting a whole table full of returns or a single row?

please attach a sample of what you have and what you want as an output

hello,

i see two ways of doing this... one is to use vlookup function, and second using index, match functions.

i've attached both ways in the spreadsheet. i suggest the second way, since it allows for more future options. with index, you may change the titles of each column, move them, etc, and still get the value, without regards to which actual column it's in.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

in this case, for ease, you may name the stock # prompt as Stock, or something similar, so that it's easier to click and drag (the ability maintains the variable, while moving only the associated other factors). for further ease, such that using the AutoFill -click and drag- is more compatible, you may have a total of 4 "Insert->Name->Define"s. 1)StockChart - selected as columns A through E. 2) Stock - selected as the cell in which you wish to enter the stock number. 3) Row - selected as A:A. & 4) Column - selected as 1:1.

this will make it extremely easy for clicking and dragging, not to mention the code:

Please Login or Register  to view this content.

click and drag as needed... and a lot easier to read

no problem please remember to mark forum as [solved]