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))

 

[PaddyD]

The board interprets less than signs as html tags when they are followed by a letter. Just add a space in the formula manually before you post:

=IF($E3="","",IF($E3< something, something....

 

[RudeClown]

Makes sense, thank you PaddyD!

Jasonb75, here is what I came up with by adopting your formula that seems to work, what do you think?

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

 

[jasonb75]

Seeing the #REF ! error in there, I guess that you've been deleting rows / columns.

I don't see any problems with your variation to the formula, but trimming it down a bit, I think that this should work.

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

One thing that I will point out, "1" and 1 are not the same, numbers formatted as text are overlooked by a number of mathematical functions so can quite easily cause problems with other formulas that are difficult to trace.

 

[RudeClown]

I did find an issue with my variation that gave an incorrect sprint number, though it was a scenario I didn't think I would come across. I'm working on doing it a different way to account for that, but I assume you are referring when I add the number 1 if the item was completed prior to the beginning of the first sprint? I would need the "1" to be a number, which way is number versus text? Thank you for pointing that out!

 

[jasonb75]

In a formula "1" is text, 1 is a number. In a cell that just conains a number with no formula, '1 is text.

Anything resulting from an =TEXT(...) formula is naturally text, as is anything in a cell formatted as text.

Less obvious, numbers joined together using formulas like =1&2 or =CONCATENATE(1,2) are text.

Numbers in text format can be coerced in many ways as long as they are valid, a few examples
=--(1&2)
=TEXT(...)+0
=CONCATENATE(1,2)*1
=VALUE(text number)

Although it would be preferable to use proper numbers to start with where possible.

 

[RudeClown]

Hi jasonb75,

I had to rework my conditions as to whether I used yourformula, or your formula -1, but after reworking it, and adding another instanceof error checking, I have a final formula that is working as expected. I’ve reviewed almost 300 entries on List!,and all outputs to the “Completed During Sprint #” column (List!F) are correctwith this formula. I had to use somecolumns not yet brought into the mix, List!R is the sprint number an item wasassigned in, List!S is the starting date of the sprint the item was assignedduring, and List!T is the ending date of the sprint the item was assignedduring. I truly appreciate your help inarriving to this solution!


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

 

[jasonb75]

If I'm following the List! column references correctly, I can see you do have a number of functions that you don't need. I think that I might be getting something mixed up around the part of E2=S2, it looks as if you have changed the logic there compared to earlier posts.

As it was established earlier that the start of a sprint is the end of the previous sprint, it is only necessary to test the date of interest against the starting dates.

=IF($E2="","",IF($R2="","ENTRYERROR",IF($E2<=Sprints!$B$2,1,INDEX(Sprints!$A$2:$A$271,MATCH($E2-1,Sprints!$B$2:$B$271,1)))))

$E2< Sprints!$B$2 changed to $E2<= because the later part is now working with $E2-1. Without this change any exact match to $E2 would be missed.

Using MATCH($E2-1 is comparing the day before the date in $E2 to the staring dates instead of the actual date, this means any date in $E2 that is an exact match to the a starting date will be matched to the previous row instead.

 

[RudeClown]

Hi Jasonb75,
The logic I used here is as follows:

1. If the item has not been completed, List!E willbe blank. Return blank.
   
2. If the item has not yet been assigned, but thereis a completed date, display “ENTRY ERROR” so the user knows to correct theline item by adding the sprint the item was assigned during (List!R).
   
3. If the item was completed before the beginningof the first sprint (List!E < Sprints!B2), we want it counted as having beenpart of Sprint 1, return 1.
   
   
4. Determine if the completed date matches a beginning/endingdate of any of the sprints. If it does NOT,then run the formula and return the correct sprint.
   
5. If it DOES, we need to determine if it shouldcount as having been completed on the last day of the earlier sprint, or on thefirst day of the new sprint. To do this,check if the completed date (List!E) matches the start date of the sprint theitem was assigned during (List!S). Ifso, we know it was completed on the first day it was assigned, and the formulashould run without the minus one so it returns the latter sprint.
   
6. If it does not match, then it was eithercompleted late, the last day of the sprint it was assigned, OR was completedearly. Any of those three scenarios, wewant counted as the earlier of the two sprints, so run the formula to pull thesprint number and subtract one so the earlier of the two is returned.
   

 

[RudeClown]

I apologize for all the words running together, I am drafting in Word, then copying/pasting in here because of the insanely short time-out. When I paste, it looks fine, but then when it posts, it combines a bunch of the words.

 

[jasonb75]

I apologize for all the words running together, I am drafting in Word, then copying/pasting in here because of the insanely short time-out. When I paste, it looks fine, but then when it posts, it combines a bunch of the words.

Not sure what issue you're running into there, I have spent around 30 minutes typing replies before with no issues. If all else fails, try tying your reply here, then copy, refresh and paste.

Only had a quick glance at this due to shortage of free time today, but I think that I have this right, $E2-($E2<>$S2) means if E2 and S2 are the same then it will use that date, otherwise it will subtract 1 day to pick up the previous sprint. If I have the logic backwards there then simply changing <> to = should fix it.

=IF($E2="","",IF($R2="","ENTRYERROR",IF($E2<=Sprints!$B$2,1,INDEX(Sprints!$A$2:$A$271,MATCH($E2-($E2<>$S2),Sprints!$B$2:$B$271,1)))))

Maybe try it in a different column so that you can easily compare the results to the longer formula.