Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 🙂

excel
MS Office Version: Binary
posted by: Cassilee on Thu Oct 17, 2019 at 9:55 pm
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.

posted by: Variatus (rep: 4889) on Fri Oct 18, 2019 at 6:34 am
Discuss


Answer the Question

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