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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

L
  • Question Question
INDEX MATCH
Replies
1
Views
334
Fluff
Fluff
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
404
Alex Blakenburg
A
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
277
RoryA
RoryA
B
  • Solved
formula to lookup count or rank of 100 in list of dates
2 3
Replies
20
Views
367
Fluff
Fluff
k3yn0t3
  • Question Question
Software Biz Analysis for Investing: How to break down customer data based on multiple criteria and show on new tabs
Replies
1
Views
432
k3yn0t3
k3yn0t3

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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