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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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 ?
 
Upvote 0
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
 
Upvote 0
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​

Power Query:
// 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
 
Last edited by a moderator:
Upvote 0
or with one step:

Power Query:
// 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

result is the same as in previous post

on the end set custom format for Machine Stop column
 
Last edited by a moderator:
Upvote 0
I prefer sandy's approach or unpivoting/repivoting via the wizard in excel. There are formula solutions though:

Book1
ABCDEF
1Machine IDSizeBatch (1)Machine Stop (1)Batch (2)Machine Stop (1)
21SmallABC-10147/9/19 20:35ABC-10207/8/19 17:13
32MediumABC-11157/8/19 15:54ABC-10817/8/19 19:30
43LargeABC-10167/8/19 11:13ABC-11427/10/19 0:11
51MediumABC-10177/10/19 1:16ABC-12037/10/19 9:52
6
7Machine IDSizeBatch #Machine Stop
83Large7/8/19 11:13ABC-1016
92Medium7/8/19 15:54ABC-1115
101Small7/8/19 17:13ABC-1020
112Medium7/8/19 19:30ABC-1081
121Small7/9/19 20:35ABC-1014
133Large7/10/19 0:11ABC-1142
141Medium7/10/19 1:16ABC-1017
151Medium7/10/19 9:52ABC-1203
Sheet4
Cell Formulas
RangeFormula
A8:B15A8=INDEX(A$2:A$5,IFERROR(MATCH($C8,$D$2:$D$5,0),MATCH($C8,$F$2:$F$5,0)))
C8:C15C8=SMALL(CHOOSE({1,2},$D$2:$D$5,$F$2:$F$5),ROW(A1))
D8:D15D8=IFERROR(INDEX($C$2:$C$5,MATCH(C8,$D$2:$D$5,0)),INDEX($E$2:$E$5,MATCH(C8,$F$2:$F$5,0)))
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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