INDEX MATCH fails for unique value

Options

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.

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    Try adding ,0 at the end of the formula.

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    Try adding ,0 at the end of the formula.

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • amandakc
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!