Index match function with multiple criteria including and/or

0

Hi, I'm new to the index match formula and I'm so close to achieving what I'm after but I'm stuck and need some help.

I'm trying to MATCH "EID" and COURSE (Course code1 or 2 or 3) and display the indexed date for that match.

In the picture my formula works beautifully But not if there is a previous EID match (e.g. F3:H3). If I delete these cells it works, but I need it to sift past this. I think I need and AND statement but after many attempts I've been unsuccessful. 

My formula including the AND looked like this:

{=IFERROR(INDEX(F3:H17,MATCH(1,(AND(F3:F17=A6)*(OR(G3:G17=B3,G3:G17=B4,G3:G17=B5))),0),3),"")}

Please HELP 🙂

Answer
Discuss

Answers

0

I don't think your idea can be implemented in Excel. This is because the OR() function will return True if a match is found in any row (not the prticular row where A6 was found. Therefore your formula, when corrected/simplified as shown below, will return the result from the first row where A6 ws found, provided only that the OR() function returns a True.

=IFERROR(INDEX(F3:H17,MATCH(1,(F3:F17=A6)*OR(G3:G17=B3,G3:G17=B4,G3:G17=B5),0),3),"")

To overcome the problem ou might try to stack the 3 OR conditions, like

IFERROR(MATCH(1,(F3:F17=A6)*(G3:G17=B3)),0),IFERROR(MATCH(1,(F3:F17=A6)*(G3:G17=B4)),0),IFERROR(MATCH(1,(F3:F17=A6)*(G3:G17=B5)),0),0)

So, if none of the three possible matches works out the MATCH() will return 0 which should trigger the initial IFERROR condition in your formula.

I didn't try this idea out. If you do, please share gour experience here.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login