VLOOKUP Problem has me stumped

[pongmeister]

I'm not getting expected result from this vlookup formula:

Please Login or Register  to view this content.

It is returning a value of "T1"


The code is in worksheet CA_RR1_D cell BO9

$A9 has value of 2 and since there is no value 2 IN COLUMN W i don't expect any value to be returned -

Can I set a default value if the lookup criteria is not found?

[berlan]

Try,

=IFERROR(VLOOKUP($A9,CA_RR1_FINAL!$W$3:CA_RR1_FINAL!$X$46,2,FALSE),"no match")

change the second parameter of iferror for the text/value to be returned if no match. Also added FALSE to the last parameter of vlookup to give a more exact match, if any.

[pongmeister]

Try,

=IFERROR(VLOOKUP($A9,CA_RR1_FINAL!$W$3:CA_RR1_FINAL!$X$46,2,FALSE),"no match")

change the second parameter of iferror for the text/value to be returned if no match. Also added FALSE to the last parameter of vlookup to give a more exact match, if any.

I tried the code changes but get back: #NAME? I suspect the IFERROR function is not available in excel 2003 because when I type =IFERROR( I don't get the usual syntax prompt as i normally get with a formula once I put in the left parenthesis.

Thank you for your help - looks like that would have worked if I had a newer version of excel - Please let me know if you think of another solution - I do have a work around but it's not the most ideal - basically I do the vlookup and if it returns a value not equal to my search criteria I set a flag in another cell to say 'Drop' Drop means i didn't get a match.

[FDibbins]

You are correct, IFERROR() is not available until 2007. Try this instead...
=IF(ISERROR(VLOOKUP($A9,CA_RR1_FINAL!$W$3:CA_RR1_FINAL!$X$46,2,0)),"",VLOOKUP($A9,CA_RR1_FINAL!$W$3:CA_RR1_FINAL!$X$46,2,0))

[Tony Valko]

I didn't download your file.

What type of data does the formula normally return? Text? Numbers? Could be either? Something else?

[Tony Valko]

No need in repeating the sheet name for a 2d range reference.

=IF(ISERROR(VLOOKUP($A9,CA_RR1_FINAL!$W$3:$X$46,2,0)),"",VLOOKUP($A9,CA_RR1_FINAL!$W$3:$X$46,2,0))

[Tony Valko]

What type of data does the formula normally return?

If the formula normally returns TEXT this will return a blank instead of the error:

=LOOKUP("zzzzz",CHOOSE({1,2},"",VLOOKUP($A9,CA_RR1_FINAL!$W$3:$X$46,2,0)))

[FDibbins]

No need in repeating the sheet name for a 2d range reference.

=IF(ISERROR(VLOOKUP($A9,CA_RR1_FINAL!$W$3:$X$46,2,0)),"",VLOOKUP($A9,CA_RR1_FINAL!$W$3:$X$46,2,0))

Good catch Tony, I didnt even look at that

[pongmeister]

First column in the range for vlookup contains numbers and the second column contains alpha numeric

[pongmeister]

Tony,

This formula worked perfectly - thanks so much!!

I will mark this thread closed

[pongmeister]

Hi Tony and Ford,

Thanks for your help on this and also for the comment about repeating the sheet name for a 2nd range reference - I've been doing that for all my vlookups and didn't know you didn't need to put it for the 2nd range. That will same me a lot of typing in the future..

I will close this thread now.

[Tony Valko]

You're welcome. Thanks for the feedback!