Get rid of #N/A when using lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

systemx - 17 Mar 2006 07:40 GMT

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

Signature

systemx

Reply to this Message

systemx - 17 Mar 2006 07:51 GMT

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 :

Reply to this Message

Max - 17 Mar 2006 10:54 GMT

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

Perhaps this shorter version using MATCH would also suffice:
=IF(ISNA(MATCH($B2,Distribution!$B$12:$B$150,0)),"MY TEXT
STRING",$B2)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message