Index/Match with IFNA and IF statements

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

 

[Chip Pearson]

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

 

[Rose]

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.

 

[Aladin Akyurek]

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.

 

[RagDyer]

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.

 

[Aladin Akyurek]

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

http://tinyurl.com/33he6

 

[RagDyeR]

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