How To Return Multiple Match Values Using Index Match

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:
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
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[...]

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
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

[...]

What are we looking up - a SKU, that is, 361553?
 
Upvote 0

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