INDEX MATCH - To return all rows based on multiple conditions ("",SetA)

Hi Excel Experts,

I have data as below:-

A1 B1 C1
Jan Jimmy
Feb Jimmy TeamA
Jan Ronald TeamB
Dec Donna TeamC
Dec Doris TeamA
Mar Ronald

What I need is to return all data in the row that matches in column C="" OR "TeamA"
I have googled to find the solutions but most of them shows based on multiple criterias, not conditions. I got the below formula but it only can be applied to return based on 1 condition i entered in $A$14, but not for both. I have tried modifying this with IF statement or "OR" in the MATCH command but didn't work. Pls help.:-

{=IF(ISERROR(INDEX($A$1:$C$10,SMALL(IF($A$1:$A$10=$A$14,ROW($A$1:$A$10)),ROW($B1)),COLUMN(B1))),"",(INDEX($A$1:$C$10,SMALL(IF($A$1:$A$10=$A$14,ROW($A$1:$A$10)),ROW($B1)),COLUMN(B1))))}

Originally Posted by :) Sixthsense :)

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,
Attached is the sample for your reference.

Thank you in advance.

Originally Posted by :) Sixthsense :)

In A17 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=INDEX(A$4:A$9,SMALL(IF(($C$4:$C$9="TeamA")+($C$4:$C$9=""),ROW($C$4:$C$9)-MIN(ROW($C$4:$C$9))+1),ROW(1:1)))

Copy the A17 cell and paste it in B17 and C17 cells.

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Hi Sixthsense,

Tqvm for the formula. It works to return the values I want. Just that I cannot ommit the '0' for those blanks even after I added the ISERROR condition in front of your formula.

Originally Posted by sktneer

Try this array formula, need to confirm with Ctrl+Shift+Enter

Please Login or Register  to view this content.

and then copy it to C17 and down until you get the blank row.

Hi sktneer,

This works perfectly! Thanks alot to all! :-)