vlookup information (2003)

Topic

New Reply

You’ll have to provide more details. What do you want to appear in the display sheet, where does it come from, etc.
If possible, attach a small workbook demonstrating what you want to do.

Reply | Quote

Replies

Hello,
I have a spreadsheet that has two sheets. One contains information for dropdown menu and the second is for displaying the information. On the display sheet, I want to populate b2 base on the selection of a1. How can I do it? I thought about a index or vlookup values, but am having trouble with it. Thanks.

Reply | Quote

I do not completely understand your setup or what you want. Could you elaborate or even perhaps add a sample file?

Steve

Reply | Quote

Here is a sample sheet that shows what I am trying to do. I highlighted the vlookup formula. Thanks.

Reply | Quote

So you already have a working VLOOKUP formula. What exactly is the problem? Please try to be precise.

Reply | Quote

I thought I did, but if put a name on the highlighted field, it won’t display the value next to it which is what I wanted to do. I also wanted to make the name a drop down option. Thanks.

Reply | Quote

See attached version. I created a dynamic named range NameList, you can see its definition in Insert | Name | Define. It will resize automatically as you add or remove names.
I moved the highlighted cells to the other worksheet, added a validation dropdown list to the first cell (see Data | Validation), and modified the VLOOKUP formula.

Reply | Quote

Hans,

One last question. When there is no value in the formula (vlookup) #N/A appears. How can I have it so nothing appears if nothing is selected? Thanks.

Reply | Quote

Use this

=IF(A1=””,””,VLOOKUP(A1,OFFSET(NameList,0,0,,2),2,FALSE))

or

=IF(ISNA(VLOOKUP(A1,OFFSET(NameList,0,0,,2),2,FALSE)),””,VLOOKUP(A1,OFFSET(NameList,0,0,,2),2,FALSE))

Reply | Quote

Thank you Hans.

Reply | Quote