=VLOOKUP question

[jgarner]

I have a spreadsheet with two columns of data: A1 - School & B1 - Code. In cell A2 I use a validation list drop down menu which uses data from another sheet tab to select which school I want. In cell B2, I use VLOOKUP to auto-populate data. Here is the formula I use for B2: =VLOOKUP(A2,School:Code,2). The problem I have is that when I first open up the spreadsheet, and I haven't selected a school from the drop down menu in A2, cell B2 has #N/A in it. Is there a way around this so that cell B2 remains empty until I select a school for A2? Thanks in advance.

 

[barry houdini]

Try using a formula like

=IF(A2="","",VLOOKUP(....))

 

[hkaplan2]

A couple of ways to handle this. Here is one:

=IF(ISERROR(VLOOKUP(A2,School:Code,2)),"",VLOOKUP(A2,School:Code,2))

 

[jgarner]

Thanks for the replies.

One other issue I just came across, the =VLOOKUP returns the same value for 3 out of the 4 items I have in the column. No sure why? Its as if the VLOOKUP is not working / changing the value for some reason?

 

[barry houdini]

You should use VLOOKUP with a 4th argument of zero or FALSE, i.e.

VLOOKUP(A2,School:Code,2,0)

If you don't have the zero then VLOOKUP expects school list to be sorted ascending, if that isn't the case you may get odd results. Zero also means you only get an exact match, which seems to be what you need

 

[jgarner]

That did the trick! Thanks again.