Get rid of #N/A when using lookup
 

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,FALS E)=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
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=523442

Get rid of #N/A when using lookup
 

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,F ALSE)))

Cheers

Rob :)


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=523442

Get rid of #N/A when using lookup
 

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

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
---