Index Match On Multiple Columns

M

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

[TABLE="class: grid, width: 900"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: center"]Machine ID[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Batch (1)[/TD]
[TD="align: center"]Machine Stop (1)[/TD]
[TD="align: center"]Batch (2)[/TD]
[TD="align: center"]Machine Stop (1)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Small[/TD]
[TD="align: center"]ABC-1014[/TD]
[TD="align: center"]Tue-09-Jul-19 20:35[/TD]
[TD="align: center"]ABC-1020[/TD]
[TD="align: center"]Mon-08-Jul-19 17:13[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]ABC-1115[/TD]
[TD="align: center"]Mon-08-Jul-19 15:54[/TD]
[TD="align: center"]ABC-1081[/TD]
[TD="align: center"]Mon-08-Jul-19 19:30[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Large[/TD]
[TD="align: center"]ABC-1016[/TD]
[TD="align: center"]Mon-08-Jul-19 11:13[/TD]
[TD="align: center"]ABC-1142[/TD]
[TD="align: center"]Wed-10-Jul-19 00:11[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]ABC-1017[/TD]
[TD="align: center"]Wed-10-Jul-19 01:16[/TD]
[TD="align: center"]ABC-1203[/TD]
[TD="align: center"]Wed-10-Jul-19 09:52[/TD]
[/TR]
</tbody>[/TABLE]


Finished Data Set

[TABLE="class: grid, width: 600"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]Machine ID[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Batch #[/TD]
[TD="align: center"]Machine Stop[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Large[/TD]
[TD="align: center"]Mon-08-Jul-19 11:13[/TD]
[TD="align: center"]ABC-1016[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]Mon-08-Jul-19 15:54[/TD]
[TD="align: center"]ABC-1115[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Small[/TD]
[TD="align: center"]Mon-08-Jul-19 17:13[/TD]
[TD="align: center"]ABC-1020[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]Mon-08-Jul-19 19:30[/TD]
[TD="align: center"]ABC-1081[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Small[/TD]
[TD="align: center"]Tue-09-Jul-19 20:35[/TD]
[TD="align: center"]ABC-1014[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Large[/TD]
[TD="align: center"]Wed-10-Jul-19 00:11[/TD]
[TD="align: center"]ABC-1142[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]Wed-10-Jul-19 01:16[/TD]
[TD="align: center"]ABC-1017[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Medium[/TD]
[TD="align: center"]Wed-10-Jul-19 09:52[/TD]
[TD="align: center"]ABC-1203[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Small Across 8 Worksheets
Replies
4
Views
928
martinmc
M
S
  • Solved
Index and match not working over multiple rows
Replies
3
Views
563
Fluff
Fluff
M
  • Question Question
Complex Index match or sumproduct?
Replies
2
Views
877
Melimob
M
W
  • Question Question
Creating a date/time formula that excludes weekends including friday
Replies
5
Views
745
Eric W
Eric W
A
  • Question Question
Formula to count max employees at any time
2
Replies
13
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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