Index/Match with OR Function

[tlkravitz]

I need one of my match criteria to be an or statement. I am currently using the below

=INDEX($I$37:$I$1128,MATCH(1,INDEX((D37=$D$37:$D$1128)*(F37=$F$37:$F$1128)*($G$37:$G$1128="Base"),0,1),0)) I want the last set to look for "Base" or "Allowance"

=INDEX($I$37:$I$1128,MATCH(1,INDEX((D37=$D$37:$D$1128)*(F37=$F$37:$F$1128)*($G$37:$G$1128="Base"),0,1),0))

 

[Eric W]

Welcome to the forum.

Try:

=INDEX($I$37:$I$1128,MATCH(1,INDEX((D37=$D$37:$D$1128)*(F37=$F$37:$F$1128)*(($G$37:$G$1128="Base")+($G$37:$G$1128="Allowance")),0,1),0))

 

[tlkravitz]

Thanks Eric. For some reason; it likes and works with the "Base" but not the "Allowance".

 

[Eric W]

Interesting. The first thing I'd check is if the spelling is the same for both. In this situation, case doesn't matter. I'd also check for stray spaces in the G column. "Allowance" is not the same as "Allowance ", but we could use SEARCH or something if necessary.

 

[tlkravitz]

Yea I have tried all of those and it definitely is being difficult

 

[Eric W]

You could try:

=INDEX($I$37:$I$1128,MATCH(1,INDEX((D37=$D$37:$D$1128)*(F37=$F$37:$F$1128)*(($G$37:$G$1128="Base")+ISNUMBER(SEARCH("Allow",$G$37:$G$1128))),0,1),0))