Index Match On Multiple Columns

martinmc

New Member
Joined
Apr 21, 2019
Messages
16
Hi and good evening,how would I perform a lookup on 1 unique "date time value" in columns D and F and return their matches from columns C or E all sorted from oldest to newest (like finished Data Set below).Times and Dates in columns D and F are formatted as "ddd-dd-mmm-yy hh:mm".Also I would like to ask is it possible to use the SMALL formula on multiple sheet ranges in a workbook.
Thanking You In Advance.

Data Set

Machine IDSizeBatch (1)Machine Stop (1)Batch (2)Machine Stop (1)
1SmallABC-1014Tue-09-Jul-19 20:35ABC-1020Mon-08-Jul-19 17:13
2MediumABC-1115Mon-08-Jul-19 15:54ABC-1081Mon-08-Jul-19 19:30
3LargeABC-1016Mon-08-Jul-19 11:13ABC-1142Wed-10-Jul-19 00:11
1MediumABC-1017Wed-10-Jul-19 01:16ABC-1203Wed-10-Jul-19 09:52

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>


Finished Data Set

Machine IDSizeBatch #Machine Stop
3LargeMon-08-Jul-19 11:13ABC-1016
2MediumMon-08-Jul-19 15:54ABC-1115
1SmallMon-08-Jul-19 17:13ABC-1020
2MediumMon-08-Jul-19 19:30ABC-1081
1SmallTue-09-Jul-19 20:35ABC-1014
3LargeWed-10-Jul-19 00:11ABC-1142
1MediumWed-10-Jul-19 01:16ABC-1017
1MediumWed-10-Jul-19 09:52ABC-1203

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

martinmc

New Member
Joined
Apr 21, 2019
Messages
16
Sorry, i didn't realize i had posted this message and re-edited it twice when i had to log in again. Is there some time constraint on posting a message ?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
post link to shared excel file with representative source data and expected result.
your date/time columns are as text in the post

use google drive, one drive, drop box or any similar
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe:

with PowerQuery (Get&Transform)

Machine IDSizeBatch (1)Machine Stop (1)Batch (2)Machine Stop (1)2Machine IDSizeBatchMachine Stop
1​
SmallABC-1014Tue-09-Jul-19 20:35ABC-1020Mon-08-Jul-19 17:13
3​
LargeABC-1016
Mon-08-07-19 11:13​
2​
MediumABC-1115Mon-08-Jul-19 15:54ABC-1081Mon-08-Jul-19 19:30
2​
MediumABC-1115
Mon-08-07-19 15:54​
3​
LargeABC-1016Mon-08-Jul-19 11:13ABC-1142Wed-10-Jul-19 00:11
1​
SmallABC-1020
Mon-08-07-19 17:13​
1​
MediumABC-1017Wed-10-Jul-19 01:16ABC-1203Wed-10-Jul-19 09:52
2​
MediumABC-1081
Mon-08-07-19 19:30​
1​
SmallABC-1014
Tue-09-07-19 20:35​
3​
LargeABC-1142
Wed-10-07-19 00:11​
1​
MediumABC-1017
Wed-10-07-19 01:16​
1​
MediumABC-1203
Wed-10-07-19 09:52​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract1 = Table.TransformColumns(Source, {{"Machine Stop (1)", each Text.AfterDelimiter(_, "-"), type text}}),
    Replacw1 = Table.ReplaceValue(Extract1,"19","2019",Replacer.ReplaceText,{"Machine Stop (1)"}),
    ROC = Table.SelectColumns(Replacw1,{"Machine ID", "Size", "Batch (1)", "Machine Stop (1)"}),
    Rename = Table.RenameColumns(ROC,{{"Batch (1)", "Batch"}, {"Machine Stop (1)", "Machine Stop"}})
in
    Rename

// Table1d
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Machine Stop (1)2", each Text.AfterDelimiter(_, "-"), type text}}),
    Replace = Table.ReplaceValue(Extract,"-19","-2019",Replacer.ReplaceText,{"Machine Stop (1)2"}),
    ROC = Table.SelectColumns(Replace,{"Machine ID", "Size", "Batch (2)", "Machine Stop (1)2"}),
    Rename = Table.RenameColumns(ROC,{{"Batch (2)", "Batch"}, {"Machine Stop (1)2", "Machine Stop"}})
in
    Rename

// Finish
let
    Source = Table.Combine({Table1, Table1d}),
    Type = Table.TransformColumnTypes(Source,{{"Machine Stop", type datetime}}),
    Sort = Table.Sort(Type,{{"Machine Stop", Order.Ascending}})
in
    Sort[/SIZE]
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

or with one step:

Code:
[SIZE=1]// Query1
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ROC1 = Table.SelectColumns(Source1,{"Machine ID", "Size", "Batch (1)", "Machine Stop (1)"}),
    Extract1 = Table.TransformColumns(ROC1, {{"Machine Stop (1)", each Text.AfterDelimiter(_, "-"), type text}}),
    Replace1 = Table.ReplaceValue(Extract1,"19","2019",Replacer.ReplaceText,{"Machine Stop (1)"}),
    Rename1 = Table.RenameColumns(Replace1,{{"Batch (1)", "Batch"}, {"Machine Stop (1)", "Machine Stop"}}),
    ROC2 = Table.SelectColumns(Source2,{"Machine ID", "Size", "Batch (2)", "Machine Stop (1)2"}),
    Extract2 = Table.TransformColumns(ROC2, {{"Machine Stop (1)2", each Text.AfterDelimiter(_, "-"), type text}}),
    Replace2 = Table.ReplaceValue(Extract2,"-19","-2019",Replacer.ReplaceText,{"Machine Stop (1)2"}),
    Rename2 = Table.RenameColumns(Replace2,{{"Batch (2)", "Batch"}, {"Machine Stop (1)2", "Machine Stop"}}),
    Finish = Table.Combine({Rename1, Rename2}),
    Type = Table.TransformColumnTypes(Finish,{{"Machine Stop", type datetime}}),
    Sort = Table.Sort(Type,{{"Machine Stop", Order.Ascending}})
in
    Sort[/SIZE]

result is the same as in previous post

on the end set custom format for Machine Stop column
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
I prefer sandy's approach or unpivoting/repivoting via the wizard in excel. There are formula solutions though:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Machine ID</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Size</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Batch (1)</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Machine Stop (1)</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Batch (2)</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Machine Stop (1)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">Small</td><td style="text-align: center;;">ABC-1014</td><td style="text-align: right;;">7/9/2019 20:35</td><td style="text-align: center;;">ABC-1020</td><td style="text-align: right;;">7/8/2019 17:13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">Medium</td><td style="text-align: center;;">ABC-1115</td><td style="text-align: right;;">7/8/2019 15:54</td><td style="text-align: center;;">ABC-1081</td><td style="text-align: right;;">7/8/2019 19:30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Large</td><td style="text-align: center;;">ABC-1016</td><td style="text-align: right;;">7/8/2019 11:13</td><td style="text-align: center;;">ABC-1142</td><td style="text-align: right;;">7/10/2019 0:11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;">Medium</td><td style="text-align: center;;">ABC-1017</td><td style="text-align: right;;">7/10/2019 1:16</td><td style="text-align: center;;">ABC-1203</td><td style="text-align: right;;">7/10/2019 9:52</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Machine ID</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Size</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Batch #</td><td style="font-weight: bold;text-align: center;color: #0000FF;;">Machine Stop</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">3</td><td style=";">Large</td><td style="text-align: right;;">7/8/2019 11:13</td><td style=";">ABC-1016</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">2</td><td style=";">Medium</td><td style="text-align: right;;">7/8/2019 15:54</td><td style=";">ABC-1115</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1</td><td style=";">Small</td><td style="text-align: right;;">7/8/2019 17:13</td><td style=";">ABC-1020</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">2</td><td style=";">Medium</td><td style="text-align: right;;">7/8/2019 19:30</td><td style=";">ABC-1081</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1</td><td style=";">Small</td><td style="text-align: right;;">7/9/2019 20:35</td><td style=";">ABC-1014</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">3</td><td style=";">Large</td><td style="text-align: right;;">7/10/2019 0:11</td><td style=";">ABC-1142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1</td><td style=";">Medium</td><td style="text-align: right;;">7/10/2019 1:16</td><td style=";">ABC-1017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1</td><td style=";">Medium</td><td style="text-align: right;;">7/10/2019 9:52</td><td style=";">ABC-1203</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A8</th><td style="text-align:left">=INDEX(<font color="Blue">A$2:A$5,IFERROR(<font color="Red">MATCH(<font color="Green">$C8,$D$2:$D$5,0</font>),MATCH(<font color="Green">$C8,$F$2:$F$5,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=INDEX(<font color="Blue">B$2:B$5,IFERROR(<font color="Red">MATCH(<font color="Green">$C8,$D$2:$D$5,0</font>),MATCH(<font color="Green">$C8,$F$2:$F$5,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">=SMALL(<font color="Blue">CHOOSE(<font color="Red">{1,2},$D$2:$D$5,$F$2:$F$5</font>),ROW(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D8</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$C$2:$C$5,MATCH(<font color="Green">C8,$D$2:$D$5,0</font>)</font>),INDEX(<font color="Red">$E$2:$E$5,MATCH(<font color="Green">C8,$F$2:$F$5,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

martinmc

New Member
Joined
Apr 21, 2019
Messages
16

ADVERTISEMENT

Thank You Sandy666 for your reply , Power Query is a must to learn when i upgrade soon.
 

martinmc

New Member
Joined
Apr 21, 2019
Messages
16
Hi sheetspread, all i can say is excellent, i must say i have learned a lot from your various methods of using the IFERROR and INDEX functions and especially the combination of the SMALL and CHOOSE functions. This opened up up a lot of ideas to me from your approach to this solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,538
Messages
5,636,901
Members
416,948
Latest member
Jkpang

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
Top