Return multiple records matching multiple criteria

[Karthik]

Hi All,

I have a worksheet with the following data in Sheet1
I donot want to use advanced filter or Pivot tables

Col A Col B Col C
Ram ABC Hi-Hi
Ravi CDE Med-Med
Ban ABC Hi-Hi
ASt ABC Med-Med
Son CDE Hi-Med


In Sheet2
A1 has a value from ColB i.e, ABC
B1 has a value from ColC i.e, Hi-Hi


I need the formula to display the matching records of ColA i.e,
Ram
Ban


Similarly if the values A1 and B1 of Sheet2 are changed to CDE and
Med-Med it should display Ravi


I donot want to use advanced filter or Pivot tables


Thanks in Advance
Karthik

 

[Aladin Akyurek]

In C1 on Sheet2 enter:

=SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!C2:C6,A1:B1,0)))

A3 on Sheet2:

=IF(ROWS(A$3:A3)<=$C$1,
SMALL(IF(ISNUMBER(MATCH(Sheet1!$C$2:$C$6,$A$1:$B$1,0)),
ROW(Sheet2!$C$2:$C$6)-ROW(Shhet2!$C$2)+1),
ROWS(A$3:A3)),"")

which needs to be confirmed with control+shift+enter (not just with
enter) then copied down.

In B3 on Sheet2 enter, copy across then down:

=IF(N($A3),INDEX(Sheet2!A$2:A$6,$A3),"")

 

[Karthik]

Hi Aladin,

Thanks for trying to help me, but this doesnt seem to give the result

i did a few minor changes to this
in A3 sheet2 i put
=IF(ROWS(A$3:A3)<=$C$1,SMALL(IF(ISNUMBER(MATCH(Sheet1!$C$1:$C$5,$A$1:$B$1,0)),ROW(Sheet1!$C$1:$C$5)-ROW(Sheet2!$C$1)+1),ROWS(A$3:A3)),"")

in B3 sheet 2
=IF(N($A3),INDEX(Sheet1!A$1:A$5,$A3),"")

This gives me all rows matching data in B2 but not matching multiple
criteria in A2 and B2.

Your help is highly appreciated

 

[Karthik]

Hi Aladin,

Thanks for trying to help me, but this doesnt seem to give the result


i did a few minor changes to this
in A3 sheet2 i put
=IF(ROWS(A$3:A3)<=$C$1,SMALL(IF(ISNUMBER(MATCH(Sheet1!$C$1:$C$5,$A$1:$B$1,0­)),ROW(Sheet1!$C$1:$C$5)-ROW(Sheet2!$C$1)+1),ROWS(A$3:A3)),"")



in B3 sheet 2
=IF(N($A3),INDEX(Sheet1!A$1:A$5,$A3),"")


This gives me all rows matching data in B2 but not matching multiple
criteria in A2 and B2.


Your help is highly appreciated

 

[Aladin Akyurek]

I see I messed up with sheet names...

A3 on Sheet2 should be:

=IF(ROWS(A$3:A3)<=$C$1,
SMALL(IF(ISNUMBER(MATCH(Sheet1!$C$2:$C$6,$A$1:$B$1,0)),
ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1),
ROWS(A$3:A3)),"")

Confirmed with control+shift+enter then copied down.

B3 on Sheet2 should be:

=IF(N($A3),INDEX(Sheet1!A$2:A$6,$A3),"")

Copied across and down.

 

[Karthik]

If i change A1 in sheet 2 from ABC to CDE.
The result still remains unchanged.
Is there something i am missing.

Thanks for your help

 

[Aladin Akyurek]

My fault. The formula set treats A1:B1 as conditions that must hold for
the C-range on Sheet1, while A1 is a condition for the B-range and B1
for the C-range. What follows corrects all that...

A1:B1 on Sheet2 houses the conditions for Sheet1!B2:B6 and Sheet1!C2:C6,
respectively.

C1 on Sheet2:

=SUMPRODUCT(--(Sheet1!B2:B6=A1),--(Sheet1!C2:C6=B1))

A3 on Sheet2:

=IF(ROWS(A$3:A3)<=$C$1,
SMALL(IF(Sheet1!$B$2:$B$6=$A$1,
IF(Sheet1!$C$2:$C$6=$B$1,
ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1)),
ROWS(A$3:A3)),"")

which is confirmed with control+shift+enter then copied down.

B3 on Sheet2, copied across then down:

=IF(N($A3),INDEX(Sheet1!A$2:A$6,$A3),"")

 

[Karthik]

Thanks a ton Aladin.

This was extremely helpful.

Karthik