Incorrect Index Match Data being returned

Troy Bjelland

Me again!

I have youtubed hours of videos and cannot figure out what I am dong wrong. I have a unit name that I would to automatically return a unit number from our apparatus inventory. The formula seems to function but the fleet number does not match the apparatus name from the reference table. For Squad 2 should be unit U72001 not U72001 and Engine 8 should be U72060 not U72122. Thanks in advance to the great members of this form.

Genevieve P.

Hi @Troy Bjelland

I have to admit I'm not quite sure why the MATCH is finding the wrong value - it may have to do with how the numbers are similar and it's finding an approximate match instead of exact.

I would suggest trying an INDEX(COLLECT formula instead, as you can specify bringing back the first row where there's an exact match... try this:

=INDEX(COLLECT({CCFD Apparatus Master List Range 3}, {CCFD Apparatus Master List Range 2}, [Apparatus or Unit]@row), 1)

The 1 at the end identifies the first row that has [Apparatus or Unit]@row

If this is still bringing back incorrect results, I'd be interested to know if it's finding more than one row with your criteria on it. Can you try this to test, it should just return 1 for each row:

=COUNTIF({CCFD Apparatus Master List Range 2}, [Apparatus or Unit]@row)

I would also suggest re-creating the formula from scratch and manually identifying each of your ranges/columns again in case the first selection missed a few rows from that column somehow.

Let me know if any of this has helped!

Cheers,

Genevieve