INDEX / MATCH Formula

K

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
I hope to explain this correctly. I am needed help with an INDEX/MATCH formula to get the DATE field listed below on [TAB 1].

[TAB 1]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[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]1
[/TD]
[TD]ID
[/TD]
[TD]Product
[/TD]
[TD]Training1[/TD]
[TD]Training2[/TD]
[TD]Training3[/TD]
[TD]Training4[/TD]
[TD]DATE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Mr.X[/TD]
[TD]AAA
[/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]B1
[/TD]
[TD][/TD]
[TD]1/5/2018
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mr.X
[/TD]
[TD]BBB
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]B3
[/TD]
[TD]1/2/2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mr.Y
[/TD]
[TD]CCC
[/TD]
[TD]B2
[/TD]
[TD]C1
[/TD]
[TD][/TD]
[TD]C4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Ms.Z
[/TD]
[TD]AAA
[/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]B1
[/TD]
[TD][/TD]
[TD]1/7/2018
[/TD]
[/TR]
</tbody>[/TABLE]


[TAB 2]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Training
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Mr.X
[/TD]
[TD]A2
[/TD]
[TD]2/10/2018
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mr.X
[/TD]
[TD]B2
[/TD]
[TD]2/5/2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mr.X
[/TD]
[TD]B3
[/TD]
[TD]1/2/2018
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Mr.X
[/TD]
[TD]A1
[/TD]
[TD]1/5/2018
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Mr.Y
[/TD]
[TD]A1
[/TD]
[TD]1/5/2018
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Mr.Y
[/TD]
[TD]A2
[/TD]
[TD]2/5/2018
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Ms.Z
[/TD]
[TD]A2
[/TD]
[TD]1/7/2018
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Ms.Z
[/TD]
[TD]B1
[/TD]
[TD]2/5/2018
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Ms.Z
[/TD]
[TD]A5
[/TD]
[TD]11/20/2017
[/TD]
[/TR]
</tbody>[/TABLE]


Soooo. What I am trying to ask for [Tab 1].[Column G] is, For [Column A]/[Row 2], look at all of the training courses [Columns C - Column F] and find those matching ID/training courses in [Tab 2]; and provide me with the earliest date.

For example. Ms. Z sold product AAA. In order to sell product AAA, you can either take training A1, A2, or B1. [Tab 2] holds all the training records. So we need to look for all of Ms.Z training records, then only look at Training A1, A2, or B1; and pick the earliest date (A2 - 1/7/2018).
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try:

ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Product[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Training1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Training2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Training3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Training4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]DATE[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mr.X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AAA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/5/2018[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mr.X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BBB[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/2/2018[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mr.Y[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CCC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ms.Z[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AAA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/7/2018[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IFERROR(1/(1/SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,C2:F2)),""),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Perfect!

Can you explain what the formula is doing? The thing I am having difficulty understanding is the one divided by within this equation.
 
Upvote 0
Sure! The SUMIFS finds the sum of the items in column C that match both the name in A, and the class in B. Since the date is stored as a number, we get the date. Also, we're actually giving the formula 4 classes (C2:F2), and making it an array formula, so we get back 4 sums in an array, like this {0,1/7/2018,2/5/2018,0}. Now you want the smallest date, but we can't use SMALL (or MIN) just yet, since that would just give us 0. So we use a trick. We find the reciprocal of the numbers (1/). If the number is zero, we get the #DIV/0! error. We take the reciprocal again which turns the real values back to what they were, and leaves the errors as errors. Then the innermost IFERROR turns the errors to "", leaving us with {"",1/7/2018,2/5/2018,""}. Now we can use SMALL, since it ignores non-numeric values. Incidentally, I used SMALL instead of MIN, because if there are no matches, MIN returns 0, while SMALL returns an error. Using that fact with the outermost IFERROR prevents a date of 1/0/1900 from showing up.

Glad to help! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Index Match with multiple conditions including dates
Replies
1
Views
106
StephenCrump
S
L
  • Question Question
INDEX MATCH
Replies
1
Views
341
Fluff
Fluff
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
461
Alex Blakenburg
A
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
287
RoryA
RoryA
B
  • Solved
formula to lookup count or rank of 100 in list of dates
2 3
Replies
20
Views
381
Fluff
Fluff

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

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