mingandmong
Active Member
- Joined
- Oct 15, 2014
- Messages
- 339
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
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
Code:
[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]
Last edited: