Vlookup dependent on a validation table

S

saltnsnails

I have a cell that has a validation table that allows data to be entered that
is not available in my "list". It brings up the information pop up window
and you click ok to keep going with the information entered that is not on
the list.

Another cell has a vlookup table dependent on the data in the validation
cell. I would like for the vlookup cell to return nothing if the validation
cell had data entered on it that is not included on the validation list. If
I put the last argument in the Vlookup, it returns an N/A #. My formula
looks like this:
=IF($E$12>0, VLOOKUP($E$12,$AB$3:$AC$34, 2, 0 ), "")

Any thoughts?

Thanks!
 
S

Sheeloo

instead of your vlookup put this in the cells
in Excel 2003
=ISERROR('your VLOOKUP without quotes shown here',"",'your VLOOKUP without
quotes shown here')

in 2007 you can shorten that too
=IFERROR('your VLOOKUP without quotes shown here',"")
 
S

saltnsnails

It says I have too few arguments. :(

Sheeloo said:
instead of your vlookup put this in the cells
in Excel 2003
=ISERROR('your VLOOKUP without quotes shown here',"",'your VLOOKUP without
quotes shown here')

in 2007 you can shorten that too
=IFERROR('your VLOOKUP without quotes shown here',"")
Click to expand...
 
S

Sheeloo

Sorry, I forgot the IF statement...

Try...
=IF(ISERROR(VLOOKUP(E12,$AB$3:$AC$34, 2,
FALSE)),"",VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE ))

bascially it checks ISERROR(VLOOKUP), if true returns "", otherwise retruns
VLOOKUP

I have removed the $ around E12 so that you can copy down...
 
S

saltnsnails

AWESOME!!!! Thank you!

Sheeloo said:
Sorry, I forgot the IF statement...

Try...
=IF(ISERROR(VLOOKUP(E12,$AB$3:$AC$34, 2,
FALSE)),"",VLOOKUP(E12,$AB$3:$AC$34, 2, FALSE ))

bascially it checks ISERROR(VLOOKUP), if true returns "", otherwise retruns
VLOOKUP

I have removed the $ around E12 so that you can copy down...
Click to expand...
 
P

Peo Sjoblom

Just a heads up, in these situations it is probably better to use ISNA
instead of ISERROR since ISERROR would shield errors that can be of interest
whereas ISNA is only hiding #N/A

--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stumped on creating dependent data validation lists 1
how blank data validation cell after changing dependent cell? 5
Conditional Data Validation 2
VLookup using lookup table 1
Conditional Data Validation Options 2
Combining two non-contiguous lists into one for data validation, with one range being dynamic 0
vlookup with dropdown list 3
vlookup not working properly 5

Top