Get rid of #N/A when using lookup

S

systemx

Hi all,

I am using the following lookup statement -

=VLOOKUP($B11,Distribution!$B$12:$B$150,1,FALSE)

If the value does not exist, excel returns #N/A, which is to be
expected. However, I would like it to return a different value if the
VLOOKUP value does not exist.

ie. instead of #N/A I would like it to return a text string of my
choice.

I have tried to use an IF statement...eg

=IF((VLOOKUP($B11,Distribution!$B$12:$B$150,1,FALSE)=B11, B11, "Text
string")

However, this still returns an N/A when the value does not exist.

Can anyone suggest a way around this? I get the feeling I may be
missing something very simple :p


Cheers

Rob
 
S

systemx

Have managed to figure this one out myself with -

=IF(ISERROR(VLOOKUP($B2,Distribution!$B$12:$B$150,1,FALSE)),"MY TEX
STRING",(VLOOKUP($B2,Distribution!$B$12:$B$150,1,FALSE)))

Cheers

Rob :
 
M

Max

systemx said:
=IF(ISERROR(VLOOKUP($B2,Distribution!$B$12:$B$150,1,FALSE)),
"MY TEXT STRING",(VLOOKUP($B2,Distribution!$B$12:$B$150,1,FALSE)))
Click to expand...

Perhaps this shorter version using MATCH would also suffice:
=IF(ISNA(MATCH($B2,Distribution!$B$12:$B$150,0)),"MY TEXT
STRING",$B2)
 
Top