Get rid of #N/A when using lookup

[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

[systemx]

Have managed to figure this one out myself with -

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

Cheers

Rob

[Max]

"systemx" wrote:
> =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
---

[Max]

"systemx" wrote:
> =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
---

[Max]

"systemx" wrote:
> =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
---