×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

COMBO BOX + VLOOKUP

COMBO BOX + VLOOKUP

COMBO BOX + VLOOKUP

(OP)
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      

RE: COMBO BOX + VLOOKUP

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.

RE: COMBO BOX + VLOOKUP

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.

RE: COMBO BOX + VLOOKUP

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

RE: COMBO BOX + VLOOKUP

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.

RE: COMBO BOX + VLOOKUP

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.

RE: COMBO BOX + VLOOKUP

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close