vlookup exact match only

Posted by Don R. on January 06, 2001 4:24 AM

In vlookup I only want a value if the match is exact, otherwise I want a zero or blank. I use an if-iserror formula to accomplish this, but it is somewhat complex. Is there a simple way to do this? My forumla looks something like:
=if(iserror(vlookup(b5:f45,a1,3,false)),0,(vlookup(b5:f45,a1,3,fasle))))

Thanks, Don

Posted by Aladin Akyurek on January 06, 2001 5:16 AM

I guess your formula is

=if(iserror(vlookup(a1,b5:f45,3,false)),0,vlookup(a1,b5:f45,3,false))

I don't think you can't simplify this formula or replace it by any other formula if you insist on having a 0 or blank when no value is associated with your lookup-value.

You can however just use =vlookup(a1,b5:f45,3,false) and use formulas that can cope with #N/A-values in further processing of the results.

May I also suggest that you can move your lookup table/range (d.i., b5:f45) to a separate sheet, then select and give a distinctive/meaningful name to your lookup table/range via the Name Box (or via the option Insert, Define, Name), e.g., CLIENTS, STUDENTS, DISCOUNTS, etc. Also, use if you wish 0 instead of false and ISNA instead of ISERROR. Then you can write the VLOOKUP-part as

VLOOKUP(a1,DISCOUNTS,3,0)

Aladin

Posted by Don R. on January 07, 2001 5:11 AM