INDEX MATCH fails for unique value

amandakc

Hi, I need help figuring out why certain rows return value of the wrong unique (but close) value.

Context:
Sheet1 has a list of all Item1 & Item2 dates for the year. Each row goes down the list creating a unique number for each date. There is a helper column that concatenates the date with either Item1 or Item2.
Sheet2 processes a list of just Item1. There is a helper column that concatenates the date with "Item2". There is a column reaching out to Sheet1 to match the helper columns and grab the unique number.

=INDEX({Sheet1UniqueID}, MATCH(Sheet2HelperCol, {Sheet1HelperCol}))

This formula works for 97 out of 99 rows.
The 2 rows that fail share the same date.

Example:

SHEET 1

ID	DATE	ITEM	HELPER COL
6.1	6/5/24	item2	1/2/24item2
7.1	7/3/24	item1	7/3/24item1
7.2	7/3/24	item2	7/3/24item2
8.1	8/9/24	item2	8/9/24item2

SHEET 2

DATE	HELPERCOL	GRAB ID
6/5/24	1/2/24item2	6.1
7/3/24	7/3/24item2	7.1
8/9/24	8/9/24item2	8.1

Formula to generate HelperCol in Sheet1 is: DATE@row+Item (Item is generated from another formula)
Formula to generate HelperCol in Sheet2 is: DATE@row+"Item2"

Sheet two should be pulling the 7.2 data and not the 7.1

I've tried all sorts of switch arounds and additional helper columns etc, but I cannot figure out for the life of me why just these 2 fail to grab the correct ID.

VLOOKUP couldn't handle these at all. Open to any suggestions or ideas of what I can look at.

Mark.poole

Try adding ,0 at the end of the formula.

=INDEX({Sheet1UniqueID}, MATCH(Sheet2HelperCol, {Sheet1HelperCol},0))

Mark.poole

Try adding ,0 at the end of the formula.

=INDEX({Sheet1UniqueID}, MATCH(Sheet2HelperCol, {Sheet1HelperCol},0))

amandakc

Thank you so much, that did the trick!
It did reveal 1 issue with my HelperCal using unique data due to 1172025 (jan 17) and 1172025 (nov 7) creating the same number sequence, but that was an easy fixed by using the month name instead of the number. All is working, thanks again!