How To Return Multiple Match Values Using Index Match

[mingandmong]

Hi im using excel 2010

i want to return muliple values in column A against the match in column in coulmn B

My current formula is =IFERROR(INDEX(RECALL!F:F,MATCH($A5,RECALL!B:B,0))&"","")

It matches A5 criteria in worksheet RECALL within column B and returns values to column F

i have several of the same value in column B with dirferent values in F, using the above formula duplicates the same column F value

i have found an array formula, but require the formula ammending for my sheet, many thanks for your help

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

below is a sample below of the sheet i have found that i need to change to suit my needs

[B]Sheet1[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD]Animal[/TD]
[TD]Name[/TD]
[TD]*[/TD]
[TD]Lookup:[/TD]
[TD="bgcolor: #ffff00"]dog[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD]Duck[/TD]
[TD]Daffy[/TD]
[TD]*[/TD]
[TD]Matches:[/TD]
[TD]Goofy[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]Mouse[/TD]
[TD]Mickey[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]Pluto[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD]Mouse[/TD]
[TD]Minnie[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD]Bunny[/TD]
[TD]Bugs[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD]Pig[/TD]
[TD]Porky[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD]Dog[/TD]
[TD]Goofy[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD]Dog[/TD]
[TD]Pluto[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD][B]Spreadsheet Formulas[/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](1:1)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](1:1)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](2:2)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](2:2)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](3:3)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](3:3)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E5[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](4:4)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](4:4)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](5:5)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](5:5)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E7[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](6:6)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](6:6)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
[TR]
[TD]E8[/TD]
[TD]{=IF(ISERROR[color=#008000](INDEX[COLOR=#0000ff]($A$1:$B$8,SMALL[COLOR=#ff0000](IF[COLOR=#804000]($A$1:$A$8=$E$1,ROW[COLOR=#ff7837]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#804000](7:7)[/COLOR])[/COLOR],2)[/COLOR])[/COLOR],"",INDEX[color=#008000]($A$1:$B$8,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000]($A$1:$A$8=$E$1,ROW[COLOR=#804000]($A$1:$A$8)[/COLOR])[/COLOR],ROW[COLOR=#ff0000](7:7)[/COLOR])[/COLOR],2)[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][FONT=Arial][B]Formula Array:[/B][/FONT][FONT=Arial]
Produce enclosing [/FONT][FONT=Arial][B]{ }[/B][/FONT][FONT=Arial] by entering
formula with CTRL+SHIFT+ENTER![/FONT][/TD]
[/TR]
</tbody>[/TABLE]
 

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.excel-jeanie-html.de/index.php?f=1"] Excel Jeanie HTML 4 [/URL]

 

[Peter_SSs]

My current formula is =IFERROR(INDEX(RECALL!F:F,MATCH($A5,RECALL!B:B,0))&"","")

1. What cell is this formula in?
2. Will you be dragging the replacement formula across or down to get any other results relating to cell $A5?
3. About how many rows of data is there in the RECALL sheet?

 

[mingandmong]

Hi Peter

1) My current formula is =IFERROR(INDEX(RECALL!F:F,MATCH($A5,RECALL!B:B,0))&"","") and resides in F5
2) i will be dragging down looking at A5 constantley
3) 10,000 ( but can be reduced if calculating takes too long, it just would mean i would have to do more houskeeing on the Recall sheet)

 

[Aladin Akyurek]

Hi Peter

1) My current formula is =IFERROR(INDEX(RECALL!F:F,MATCH($A5,RECALL!B:B,0))&"","") and resides in F5
2) i will be dragging down looking at A5 constantley
3) 10,000 ( but can be reduced if calculating takes too long, it just would mean i would have to do more houskeeing on the Recall sheet)

Are you trying to return all F values from RECALL where A5 is equal to B of RECALL?

 

[kvsrinivasamurthy]

Try this ARRAY formula in E2, then drag down
=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8),""),ROW($A1))),"")
ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

 

[mingandmong]

Hi Aladin yes

 

[mingandmong]

Hi kvsrinivasamurthy

should your formula not include the worksheet that it is looking at which is RECALL, and the cell value is locked between row 1-8, mine will need to go anywhere up to 10,000 rows

 

[Aladin Akyurek]

Are you trying to return all F values from RECALL where A5 is equal to B of RECALL?

Hi Aladin yes

Do you want them in F5 downwards or in F5 on to the right? The latter would mean that you can copy the formula also for A6 etc.

 

[mingandmong]

Hi Aladin

F5 down

 

[Aladin Akyurek]

Hi Aladin

F5 down

In F4 enter:

=COUNTIFS(RECALL!$B$2:$B$1000,A$5)

In F5 control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$5:F5)<=$F$4,INDEX(RECALL!$F$2:$F$1000,SMALL(IF(RECALL!$B$2:$B$1000=A$5,
    ROW(RECALL!$F$2:$F$1000)-ROW(RECALL!$F$2)+1),ROWS(F$5:F5))),"")