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.
