Index Match formula with OR function?

Question

Hello Microsoft Excel Community,

I am using the following Index Match formula:

{=IF(ISERROR(MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)),"N/A",(INDEX(Tables!$H$8:$H$452,MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE))))}

Is it possible to change the "Match" statement From "Match(G8&H8,....." to  "Match(Or(G8&H8,G8&"All",...."?

I would like this statement to reconize both the data the cell H8 or the word "All".

I hope this makes sense. Please let me know if this is possible.

Thank you.

Chris M.

Answer 1

No, not the way you want - you need to test both:

=IF(ISERROR(MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)),IF(ISERROR(MATCH(G8&"All",Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)),"N/A",(INDEX(Tables!$H$8:$H$452,MATCH(G8&"All",Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)))),(INDEX(Tables!$H$8:$H$452,MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE))))

Answer 2

Hi,

Try this array formula

IF(OR(H8="All",NOT(ISERROR(MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)))),INDEX(Tables!$H$8:$H$452,MATCH(G8&H8,Tables!$F$8:$F$452&Tables!$G$8:$G$452,FALSE)),"N/A")

Hope this helps.

Answer 3

Jeeped,

Thank you for your help and attention in this matter. I really appreciate it.

Chris.

Answer 4

Bernie,

Thank you so much for your help. Your silution worked like a charm.

Thanks again.

Chris

Answer 5

... I would like this statement to reconize both the data the cell H8 or the word "All".

With Excel XP (aka 2002) you are going to have to duplicate a lot of the formula for checking. The shortest I could get your formula down to is,

=IF(MAX(INDEX((Tables!$F$8:$F$452=G8)*SIGN((Tables!$G$8:$G$452=H8)+(Tables!$G$8:$G$452="All"))*ROW($1:$445),,)),INDEX(Tables!$H$8:$H$452,MAX(INDEX((Tables!$F$8:$F$452=G8)*SIGN((Tables!$G$8:$G$452=H8)+(Tables!$G$8:$G$452="All"))*ROW($1:$445),,))),"N/A")

However, that solution comes with a caveat in that it will find the last match. In other words, it looks from the bottom up so if column F matches G8 and column G has a match for both H8 and All, it will take the last match which may not be desirable depending upon the layout of possible double matches. It is more likely that you want to find the first match with a direct match to the value in H8 being more desirable. If that is the case, then this would be better,

=IF(MAX(INDEX((Tables!$F$8:$F$452=G8)*(Tables!$G$8:$G$452=H8)*ROW($1:$445),,)),INDEX(Tables!$H$8:$H$452,MAX(INDEX((Tables!$F$8:$F$452=G8)*(Tables!$G$8:$G$452=H8)*ROW($1:$445),,))),IF(MAX(INDEX((Tables!$F$8:$F$452=G8)*(Tables!$G$8:$G$452="All")*ROW($1:$445),,)),INDEX(Tables!$H$8:$H$452,MAX(INDEX((Tables!$F$8:$F$452=G8)*(Tables!$G$8:$G$452="All")*ROW($1:$445),,))),"N/A"))

That will still return the last match if there are more than one match to H8 in column G, but a match to H8 takes precedence to All.

Note that those are standard formulas and do NOT require Ctrl+Shift +Enter.