INDEX MATCH with OR multiple criterias

Hye there Excel experts,

I have this formula:-

=INDEX($H$2:$H$11,MATCH(1,($B4=$B$2:$B$11)*(("New Record"=$D$2:$D$11)+("OSB_USR"=$C$2:$C$11)),0)))

with Shift+Ctrl+Enter

My objective is to have the value in columnH IF it matches :-
a) value in colB AND "New Record" in colD
OR
b) value in colB AND "OSB_USR" in colC

but it seems like when I run the above formula, it returns #N/A. If I breaks them as single pair criterias, it will return me the value I'm expecting, but I need both pair of criterias.
Can anyone help me to modify the above formula to achieve this objective, please?

Thanks in advance.

A learning fella,
DZ

Try

=IFERROR(INDEX($H$2:$H$11,MATCH(1,($B4=$B$2:$B$11)*("New Record"=$D$2:$D$11),0)),INDEX($H$2:$H$11,MATCH(1,($B4=$B$2:$B$11)*("OSB_USR"=$C$2:$C$11),0)))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

You will get N/A (with your formula) if both C and D contain the specified criteria in the same row.

Originally Posted by JohnTopley

Try

=IFERROR(INDEX($H$2:$H$11,MATCH(1,($B4=$B$2:$B$11)*("New Record"=$D$2:$D$11),0)),INDEX($H$2:$H$11,MATCH(1,($B4=$B$2:$B$11)*("OSB_USR"=$C$2:$C$11),0)))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

You will get N/A (with your formula) if both C and D contain the specified criteria in the same row.

Thanks for the suggested formula, JohnTopley. However, I tried it already and unfortunately, it returned me more errors than previously.

Worked OK for me.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi p24leclerc / JohnTopley ,

After considering what p24leclerc mentioned and trying to modify the formula given by JohnTopley earlier, I managed to get this formula :-

=IF($S82="","",IFERROR(INDEX($H$2:$H$630,MATCH(1,($B82=$B$2:$B$630)*("New Record"=$D$2:$D$630),0)),INDEX($H$2:$H$630,MATCH(1,($B82=$B$2:$B$630)*("OSB_USR"=$C$2:$C$630)*("Unassigned"=$G$2:$G$630),0))))

It works! Finally... Thanks guys. Really appreciate the help.

DZ

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.