How To Return Multiple Match Values Using Index Match

M

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
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:
Sort by date Sort by votes
Hi Aladin
Thankyou for your reply, however there is no result in F5, below is your result, below taht is a sample of data on recall sheet, below that is what i hoping for as the end result where your formulas would be entered
Code: 
[B]Conty[/B]

[TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 7, align: center"]S.R.A.[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]DATE[/TD]
[TD="colspan: 2, align: left"]31/01/2016[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]REASON CODE[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]SKU[/TD]
[TD]DESCRIPTION[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]#N/A[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]Grand Total[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]2[/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]B2[/TD]
[TD]=TODAY()[/TD]
[/TR]
[TR]
[TD]F4[/TD]
[TD]=COUNTIFS(RECALL!$B$2:$B$1000,A$5)[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]=I3[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[TD]=IFERROR(INDEX[COLOR=#008000](RECALL!C:C,MATCH[COLOR=#0000ff]($A5,RECALL!B:B,0)[/COLOR])[/COLOR]&"","")[/TD]
[/TR]
[TR]
[TD]F5[/TD]
[TD]{=IF(ROWS[COLOR=#008000](F$5:F5)[/COLOR]<=$F$4,INDEX[COLOR=#008000](RECALL!$F$2:$F$1000,SMALL[COLOR=#0000ff](IF[COLOR=#ff0000](RECALL!$B$2:$B$1000=A$5,ROW[COLOR=#804000](RECALL!$F$2:$F$1000)[/COLOR]-ROW[COLOR=#804000](RECALL!$F$2)[/COLOR]+1)[/COLOR],ROWS[COLOR=#ff0000](F$5:F5)[/COLOR])[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G5[/TD]
[TD]=I4[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]=J3[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[TD]=IFERROR(INDEX[COLOR=#008000](RECALL!C:C,MATCH[COLOR=#0000ff]($A6,RECALL!B:B,0)[/COLOR])[/COLOR]&"","")[/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]=J4[/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]

Recall sheet
Code: 
[B]RECALL[/B]

[TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]BARCODE[/TD]
[TD="align: center"]SKU[/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]SUPPLIER[/TD]
[TD="align: center"]PALLET ID[/TD]
[TD="align: center"]RETURNED REASON[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]336[/TD]
[TD="align: right"]3030053055716[/TD]
[TD="align: center"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD="align: center"]CONTY[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Appliance has intermittent power cuts/Doesn’t turn on[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]337[/TD]
[TD="align: right"]3030053055716[/TD]
[TD="align: center"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD="align: center"]CONTY[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Burning smell/sparks at plug.[/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]B336[/TD]
[TD]=(INDEX[COLOR=#008000]('Barcode Data'!B:B,MATCH[COLOR=#0000ff]($A336,'Barcode Data'!A:A,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]C336[/TD]
[TD]=IF(ISNA[COLOR=#008000](B336)[/COLOR],"THIS PRODUCT IS NOT PART OF THE RECALL",INDEX[COLOR=#008000]('Barcode Data'!C:C,MATCH[COLOR=#0000ff]($B336,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]D336[/TD]
[TD]=IF(ISNA[COLOR=#008000](B336)[/COLOR],"",INDEX[COLOR=#008000]('Barcode Data'!E:E,MATCH[COLOR=#0000ff]($B336,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]E336[/TD]
[TD]=IF(ISNA[COLOR=#008000](B336)[/COLOR],"",INDEX[COLOR=#008000]('Barcode Data'!D:D,MATCH[COLOR=#0000ff]($B336,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]B337[/TD]
[TD]=(INDEX[COLOR=#008000]('Barcode Data'!B:B,MATCH[COLOR=#0000ff]($A337,'Barcode Data'!A:A,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]C337[/TD]
[TD]=IF(ISNA[COLOR=#008000](B337)[/COLOR],"THIS PRODUCT IS NOT PART OF THE RECALL",INDEX[COLOR=#008000]('Barcode Data'!C:C,MATCH[COLOR=#0000ff]($B337,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]D337[/TD]
[TD]=IF(ISNA[COLOR=#008000](B337)[/COLOR],"",INDEX[COLOR=#008000]('Barcode Data'!E:E,MATCH[COLOR=#0000ff]($B337,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TD]E337[/TD]
[TD]=IF(ISNA[COLOR=#008000](B337)[/COLOR],"",INDEX[COLOR=#008000]('Barcode Data'!D:D,MATCH[COLOR=#0000ff]($B337,'Barcode Data'!B:B,0)[/COLOR])[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/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]

expected results using index match against sku
Code: 
[B]Conty[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:103px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:68px;"><col style="width:390px;"><col style="width:63px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 7, align: center"]S.R.A.[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]DATE[/TD]
[TD="colspan: 2, align: left"]31/01/2016[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]REASON CODE[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]SKU[/TD]
[TD]DESCRIPTION[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]Appliance has intermittent power cuts/Doesn’t turn on[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]Burning smell/sparks at plug.[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Grand Total[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]2[/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]B2[/TD]
[TD]=TODAY()[/TD]
[/TR]
[TR]
[TD]F4[/TD]
[TD]=COUNTIFS(RECALL!$B$2:$B$1000,A$5)[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]=I3[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[TD]=IFERROR(INDEX[color=#008000](RECALL!C:C,MATCH[COLOR=#0000ff]($A5,RECALL!B:B,0)[/COLOR])[/COLOR]&"","")[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[TD]=IFERROR(INDEX[color=#008000](RECALL!C:C,MATCH[COLOR=#0000ff]($A6,RECALL!B:B,0)[/COLOR])[/COLOR]&"","")[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]=J3[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]=IFERROR(INDEX[color=#008000](RECALL!C:C,MATCH[COLOR=#0000ff]($A7,RECALL!B:B,0)[/COLOR])[/COLOR]&"","")[/TD]
[/TR]
[TR]
[TD]G7[/TD]
[TD]=J4[/TD]
[/TR]
</tbody>[/TABLE]
[/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:
Upvote 0
mingandmong said:
Hi Aladin
Thankyou for your reply, however there is no result in F5, below is your result, below taht is a sample of data on recall sheet, below that is what i hoping for as the end result where your formulas would be entered[...]
Click to expand...

The formula should work as advertised. If it doesn't, try to post a relevant sample, not formulas.
 
Upvote 0
Hi Aladin
sheets without formula,

recall sheet just 2 row sample from over a 1,000 rows, index match only returns one result and not multiple results that i require
Code: 
[B]RECALL[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:132px;"><col style="width:70px;"><col style="width:338px;"><col style="width:107px;"><col style="width:107px;"><col style="width:501px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]BARCODE[/TD]
[TD="align: center"]SKU[/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]SUPPLIER[/TD]
[TD="align: center"]PALLET ID[/TD]
[TD="align: center"]RETURNED REASON[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]336[/TD]
[TD="align: right"]3030053055716[/TD]
[TD="align: center"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD="align: center"]Coty[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Appliance has intermittent power cuts/Doesn’t turn on[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]337[/TD]
[TD="align: right"]3030053055716[/TD]
[TD="align: center"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD="align: center"]Coty[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Burning smell/sparks at plug.[/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]

expected results on second sheet using index match against recal sheet

Code: 
[B]Coty[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:103px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:68px;"><col style="width:390px;"><col style="width:63px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="colspan: 7, align: center"]S.R.A.[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]DATE[/TD]
[TD="colspan: 2, align: left"]01/02/2016[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]SKU[/TD]
[TD]DESCRIPTION[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]REASON CODE[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]Appliance has intermittent power cuts/Doesn’t turn on[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]361553[/TD]
[TD]BABYLISS POWERLIGHT 2000 HAIRD[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]Burning smell/sparks at plug.[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Grand Total[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]2[/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]
 
Upvote 0
mingandmong said:
Hi Aladin
sheets without formula,

recall sheet just 2 row sample from over a 1,000 rows, index match only returns one result and not multiple results that i require

[...]

expected results on second sheet using index match against recal sheet

[...]
Click to expand...

What are we looking up - a SKU, that is, 361553?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Index with Multiple Conditions
Replies
2
Views
179
kivikatz
K
H
  • Solved
Adding Match to an Index Formula
Replies
3
Views
174
Fluff
Fluff
C
  • Question Question
Conditional Formatting with multiple criteria for first instance only
Replies
1
Views
144
Cynthtmr
C
D
  • Solved
List of items within a specific month
Replies
4
Views
552
Fluff
Fluff
Asbestos_Jen
  • Solved
Speed up slow array index calculations
Replies
3
Views
193
Scott Huish
Scott Huish

Share this page

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