Index/Match with IFNA and IF statements

  • Thread starter Thread starter Rose
  • Start date Start date
R

Rose

I'm positive I asked this recently, but cannot find the
post or the sheet I used this on.

I have a pretty standard lookup situation, but LOOKUP is
returning the nearest value if it doesn't find a match. I
need for the formula to do a lookup for exact matches only,
and then perform an IF test on it. If the lookup result is
N/A, then replace it with a 0.

Blessings on the person to answer, and happy Friday to all!
 
Rose,

Use VLOOKUP or HLOOKUP, and set the last parameter to FALSE. E.g,

=IF(ISERROR(VLOOKUP(1,A1:B10,2,FALSE)),0,VLOOKUP(1,A1:B10,2,FALSE
))
 
Tried doing just the VLOOKUP part to test it, and it's not
working.

=VLOOKUP($A22,'Jul 02'!$E$2:$E$150,-4,TRUE)

My lookup values are in the sheet Jul 02, the match will be
in the E column, and the values to return in the A column.
 
C22:

=MATCH($A22,'Jul 02'!$E$2:$E$150,0)

B22:

=IF(ISNUMBER(C22),INDEX('Jul 02'!$A$2:$A$150,C22),"")

B22 is the result cell.

If an approximate match is appropriate, change 0 to 1 in the MATCH formula.
 
Curious!
Is there a reason Aladin, why you used two formulas instead of just a single
one?

=IF(ISNA(MATCH(A22,'Jul 02'!E2:E150,0)),"",INDEX('Jul
02'!A2:A150,MATCH(A22,'Jul 02'!E2:E150,0)))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


C22:

=MATCH($A22,'Jul 02'!$E$2:$E$150,0)

B22:

=IF(ISNUMBER(C22),INDEX('Jul 02'!$A$2:$A$150,C22),"")

B22 is the result cell.

If an approximate match is appropriate, change 0 to 1 in the MATCH formula.
 
Very enlightening!
Thanks
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Efficiency/speed is the sole reason: The set up avoids computing the same
thing twice. A related discussion:

http://tinyurl.com/33he6
 
Back
Top