INDEX/MATCH Lookup Assistance

[RudeClown]

Hello,

I amjust beginning to learn VLookup, but have found that I will need to useINDEX/MATCH for one of the lookups I need to perform, as the key will not be inthe far left column. I am struggling tofigure out how to accomplish what I need, as I have not yet used thesefunctions. I was hoping somebody mightbe able to give me a hand, as I’ve seen different syntaxes for these functions. Here’s my layout:

“List” tab

ColumnE: date (MM/DD/YYYY)

Column F: This column needs to take the date located in‘List’! column E, and locate it on the table found on ‘Sprints’!A2:P271, andreturn the sprint number that the date falls within (found in ‘Sprints’! columnA.

“Sprints” tab

ColumnA: Sprint Number

Columns B – P: Each sprint is 14 days in length, and eachcolumn displays one date within the 14 day timeframe. For example, sprint 1 is 8/27/19 – 9/10/19,so column B shows 8/27/2019, column C shows 8/28/2019, etc.

This is what I have tried, but am getting a #N/A error, eventhough the date in question is located on the table. Any guidance would be appreciated!

=INDEX(Sprints!A2:$A271,MATCH(E3,Sprints!B2:P271,0))

 

[jasonb75]

Match can only be 1 row or column, you can work around it assuming that your dates are consecutive with B3 being the day after P2, B4 being the day after B3, etc. by using

=INDEX(Sprints!$A$2:$A2$71,MATCH(E3,Sprints!$B$2:$B$271,1))

Otherwise you would need an array to find the correct row.

 

[RudeClown]

The dates are consecutive, other than the final date of sprint 1 (row 2) and the first date of sprint 2 (row 3) are the same. So, B2-P2 are consecutive in row 2 (8/27 - 9/10), then row 3 starts with a duplicate of 9/10 and goes up to 9/24, etc.

 

[jasonb75]

Ok, so in the case that the date in E3 is 9/24, which result should the formula show from the Sprints sheet, the content of A2, or that of A3?

 

[pella88]

Hi RudeClown,

Is there are a reason to have 14 consecutive days in Sprints sheet? I am asking, because a simple table with 3 columns: Sprint (lets assume A2:A250), Date From (lets assume B2:B250), Date To (lets assume C2:C250), would be much easier.

If you want to organize the table as mentioned, the formula would be:
=LOOKUP(2,1/(Sprints!$B$2:$B$250<=E2)/(Sprints!$C$2:$C$250>=E2),$A$2:$A$250)

This would return the appropriate sprint for which date from column E is between dates in B and C columns from Sprint sheet.

If you still want to keep the structure as you mentioned, then the solution is as follows:
=INDEX(Sprints!$A$1:$A$271,SUMPRODUCT((Sprints!$B$1:$B$271=E2)*ROW(Sprints!$A$1:$A$271)))

Maybe a bit overcomplicating, but it will yield correct result.

Br
pella88

 

[RudeClown]

First off, thank you both for your help, I truly appreciateit! I’m looking forward to dissecting thefinal answer here to help me learn how these other lookup tools work for futureneeds!

Jasonb75: The result givenfrom column A should be the sprint number in the same row as where the date wasfound. Since 9/24 is in both sprints, I’dideally want it to show the earlier of the two (So A2, in your question). It looks like this is showing the latter withyour formula (showing A3). It’s not abig issue….but is there a way to adjust for that?

Pella88: I started myproject with the fields as you suggested, but I thought it might be easier tohave all the dates listed, but that was before I realized I couldn’t useVLOOKUP to do the search. This is myfirst experience with any of the search functions, so I really have noexperience with them yet. VLOOKUP is theonly one I’ve used successfully on my own so far, for a different column onthis project. You’re right, if I can doit with only the beginning & end dates, that would be much cleaner andprobably easier. It would also make iteasier to adjust if a sprint were to be extended or cut short. I cut the middle dates out of the “Sprints”tab to try it. I had to add the “Sprints”tab to the final argument in your first formula, otherwise it was returning thevalue in ‘List!A3’ instead of returning the value of ‘Sprints!A3,’ but it seemsto work with that change. For dates thatfall within two sprints (the last day of one and first day of the next), it isreturning the latter of the two. Forexample, the date in ‘List’!E5 is 9/24/19 (which is end date of sprint 2 ANDthe first day of sprint 3), it is returning “3”. As in my reply to Jason above, it would beideal if it could return “2”, do you know of a way to do that with this method? Maybe if the key from ‘List’!E is found in ‘Sprints’!C,subtract 1 from the result? Just athought.

 

[jasonb75]

The easiest way to do it would be to just have the start date for each sprint listed in column B, then use the formula from post #2

You could use something like

=IFERROR(INDEX(Sprints!$A:$A,AGGREGATE(15,6,ROW(Sprints!$B$2:$B$271)/(Sprints!$B$2:$P$271=E3),1)),"Date not found")

To work with your existing table of dates, but to be honest that is an inefficient way to do it.

The sumproduct formula that Pella suggested is a method that should be used with caution, if there is no matching date then it could return an incorrect result.

 

[RudeClown]

Hi Jasonb75,
Thank you again for your time! I did trim down my table on ‘Sprints!’ down tojust the Start Dates and End Dates of each sprint. I adapted your formula as provided in #2 . I added error checking in case the E columnis blank, I want it to display “1” if the item was completed prior to thebeginning of the first sprint, and (I think) a fix for showing the lower of thetwo sprints when an item was completed on the last/first day. I’ve added it into my sheet and SEEMS to beworking as I want it to……are there any problems that you might foresee by usingthe below?

=IF($E3="","",IF($E3<Sprints!$B$2,"1",IF(COUNTIF(Sprints!$C$2:$C$271,"="&$E3)=0,INDEX(Sprints!$A$2:$A$271,MATCH($E3,Sprints!$B$2:$B$271,1)),(INDEX(Sprints!$A$2:$A$271,MATCH($E3,Sprints!$B$2:$B$271,1))-1))))

 

[RudeClown]

=IF($E3="","",IF($E3<Sprints!$B$2,"1",IF(COUNTIF(Sprints!$C$2:$C$271,"="&$E3)=0,INDEX(Sprints!$A$2:$A$271,MATCH($E3,Sprints!$B$2:$B$271,1)),(INDEX(Sprints!$A$2:$A$271,MATCH($E3,Sprints!$B$2:$B$271,1))-1)))).

 

[RudeClown]

Well, it keeps cutting off my formula when I try to paste it and submit the message. Any thoughts on that? Lol