Forum Discussion
aquigley1
Mar 11, 2025Copper Contributor
Index Match broken after update
I am used to using Index Match to combine information from 2 tables into 1 table, matching based on a unique field, such as invoice number. Excel used to crash frequently, until IS upgraded my Excel to a more recent version. It now rarely crashes, but my formulas are broken.
Here is some test data and the formula that USED to work.
I am trying to pull the description from the 2nd table to the 1st, using the invoice # to match.
I learned to add @ to get rid of the #SPILL! error, so here is the formula with @ added:
You can see that it's copied the data for line 3 successfully, but the others don't work. I used this daily on sheets with 100s to 1000s of lines. Manually matching is not an option.
I've spent several days troubleshooting and decided it's time to ask for help. I've tried various lookup (vlookup, xlookup) formulas as well, those didn't work. I saw "Index Match Match" but I can't figure it out. Also, why require matching twice when the old formula only required it once? Seems like a downgrade.
Thank you in advance for any assistance.
- OliverScheurichGold Contributor
=INDEX(Test2[Desc],MATCH([@Invoice],Test2[Invoice],0))
This formula works in my sample file.
- aquigley1Copper Contributor
So taking out the first and third @ fixes it? Do you know what the @ signs are for? What do they do?
- OliverScheurichGold Contributor
Here are two guides on how to use structured references in dynamic tables with VLOOKUP or INDEX and MATCH and how to use @.