VLookUP and #N/A

[martin23]

Hi all,

I have the following function in one of my speedsheets

=IF(B2=VLOOKUP(B2,Excelbreakdown.xls!Senior,1,FALSE),"Yes","No")

*Senior is the name of a data range

This works fine, but where it cannot find the B2 value it it Gives #N/A

I have tryed using =isna( but am unable to get it working. also i have heard this meathod slows the spreedsheet down.

Any Help would be FAB :-)

Thanks
Martin

[duane]

Not sure why you need vlookup.....this looks for exact match of b2 in range senior and if it is there yields "yes", otherwise "no"

=if(isna(match(B2,Excelbreakdown.xls!Senior,0)),"No","Yes")

[martin23]

Hi

the function you suggested is good and the "yes" "no" works. However It seems to be giving the wrong answer on a few eg if an item is in the list it will still give a "No".

I have been reading around and wondered if this was due to the values being names Eg Bob, Martin etc. I have put the names in alphabetical order but the problem seems it persist :-(

Any ideas would be FANTASTIC

Thanks
Martin

[duane]

It is looking for an EXACT match - any extra spaces, or commas, or anything will result in "no"

[martin23]

THANKS LOTS

Problem solved !!!!

I had some of the cells Centre Aligned - this was causing them to be different !

Thanks again :-)

Martin