Forum Discussion
Solved
Match/Index referencing incorrect row
I have a spreadsheet that I am working on in MS365. On sheet "A" I am drawing the following information: AGE (=G63) into cell S65 Weight Lifted (=F68) into cell S66 From sheet "B" (Reference data)...
Thank you, that is very helpful.
The Lift score in S66 has formula =F68.
Cell F68 contains the value 90, but it is formatted as Text. So the XLOOKUP formula in S68 does not look for the number 90 but for the text value "90". This causes the error.
If you format F68 as General or as Number, then press F2 and Enter to force Excel to reevaluate the cell, the formula in S68 will return 46 and that in S69 will return 10.
Do you have Office 2021 or later or Microsoft 365 (or use Excel for the Web)?
If so:
S67: =XMATCH(S65,--LEFT(MDLAGE,2),-1)
S68: =XMATCH(S66,INDEX(MDL,0,S67),-1)
If you have an older version, rearrange the data on sheet B so that the scores in MDLPOINTS are in ascending order; otherwise MATCH won't work correctly.