Forum Discussion
Match/Index referencing incorrect row
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.
HansVogelaar,
Thank you for your response. I am using Microsoft 365. I entered the formulas you provided; however, the XMATCH for cell S68 is still returning the incorrect row - row 3. Based on the data entered (age 54) and the weight lifted (200#) it should return row 31. See MDL chart below:
No, look carefully. In column I, the column for age group 52-56, the 200 is in row 29, not row 31.
My apologies, you are correct; however, the XMATCH formula, XMATCH(S66,INDEX(MDL,0,S67),-1) is returning row 3, not row 29 as it should.