COMBO BOX + VLOOKUP

[paullaup]

I'm having real trouble with this!
I have a basic combo box with a list of 7 items from a materials table, with a link to cell A7. I also have a vlookup function that pulls from a table coresponding to the value selected in cell A& (combo box).

The thing is this: When I select a value from the combo box, the correct value will be shown in the vlookup box for certain combo box selections only! (coresponding to the row that the selected value came from), BUT for some other values in the combo box, the vlookup will pull from a completely different row?? any ideas?
OH,.. my vlookup looks like this: =vlookup(B7,B10:E16,2)

HERES A SAMPLE OF MY TABLE:


MATERIAL VALUE-1 VALUE-2 VALUE-3
A A1 A2 A3
B B1 B2 B3
C C1 C2 C3
D D1 D2 D3
E E1 E2 E3
F F1 F2 F3
G G1 G2 G3

 

[melone]

Make sure your list has the $'s. vlookup(B7,$B$10:$E$16,2)

This will keep the loopup range constant, even when you copy the formula to different cells.

 

[joerd]

and also disable the range lookup : vlookup(B7,$B$10:$E$16,2,FALSE)

I don't know why Excel has range lookup enabled by default, I always include the "False" argument in lookups.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[lilliput1]

Also when using lookups, the table base lookup value must be in ascending order. Verify this is so by using the Data Sort function.

 

[bpeirson]

If you include the FALSE argument as Joerd describes Excel will look for an excact match and sorting is not required.

However, if you have duplicate entries in the lookup range excel will locate the one closest to the top of the list.

 

[yakpol]

paullaup,
Use INDEX function instead of VLOOKUP. The combobox link cell returns a number (not a value in the combobox!)

Result = INDEX(B10:E16,B7,Ncolumn)
Where Ncolumn is a column number you want to retrieve, and
B7 is the link cell to combobox

Alternatevely, use data validation - list option instead of combo box, then VLOOKUP(B10:E16,cell_with_data_validation,Ncolumn) will be appropriate.

Good luck.

 

[jgreg43]

Ive done this before but do not recall how to write the formula. I want to take a result in a cell and go to a table and look it up on the left hand side and then go along the top of the table based on a number I provide, and then go down to the point the two come together, then place the number in my cell. I know it's fairly easy but do not recall how. Any help would be appreciated.