Index & Match (list more than one match)

Status
Not open for further replies.

Rudipoo

New Member
Joined
Oct 30, 2008
Messages
13
ABCDEF
1HA Test 1HA Test 2HA Test 3Criteria
2Jim10/09/17 (C)10/09/17 (A)10/09/17 (C)HA Test 1
3Jane10/09/17 (A)10/09/17 (C)10/09/17 (A)10/09/17 (A)
4Roger10/09/17 (A)10/09/17 (C)10/09/17 (C)
5Tim10/09/17 (A)10/09/17 (A)10/09/17 (C)

<tbody>
</tbody>

Hi All,

I have a problem that i cant resolve. (i have looked on this site but unable to find)

I am trying to list out names relating to criteria which = "HA Test 1" & "date" & "(A)", i can find 1 name using: =INDEX($A$2:$A$5,MATCH($F$3,INDEX($B$2:$D$5,0,MATCH($F$2,$B$1:$D$1,0)),0),0) which will = Jane but i also want the formula to show the next 2 (Roger & Tim). Therefore when changing the criteria to "HA Test 2" it will return Jim & Tim.

I have tried using: =IF(ROWS($H$13:H13)>$J$13,"",INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$F$3,ROW($B$2:$D$5)-ROW(B$2:D$2)+1),ROWS($H$13:H13)))) with the use of a helper cell = $J$13 (this would only relate to the number of names that meet that criteria).

Any help would be great,

Regards

Rudi
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Status
Not open for further replies.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top