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)
Row 2 are the age categories (17 - 21, 22 - 26, 27 - 31, 32 - 36, 37 - 41, 42 - 46, 47 - 51, 52 - 56, 57 - 61, 62 Over. These are grouped as MDLAGE.
Column A are the scores - 100 - 0 descending order. these are grouped as MDLPOINTS
In cells B3:K49 are the weights lifted. The values vary by age and there are cells that are blank or have a "---" in them. These are grouped as MDL.
The formula I am using is:
Cell S67 - =MATCH(S65,--LEFT(MDLAGE,2),1) This formula is returning the correct column.
Cell S68 - =MATCH(S66,INDEX(MDL,0,S67),1) This formula is referencing the INCORRECT row. It is returning row 40 instead of row 31 which is where the correct weight is listed.
If I change the 1 to a 0 or -1 the return is N/A. I have tried multiple formulas to "skip" the blank or --- cells but nothing has worked.
Any help would be greatly appreciated!
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.
13 Replies
You are correct, I misspoke, regardless... the XMATCH formula is returning row 3 not row 29. Thoughts?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HansVogelaar,
Please try this link since MS will not allow me to drop the file here in their platform.
https://www.dropbox.com/scl/fi/71whmw23dph9759x3htw4/3rd-PLATOON-DATA-MATRIX-5.20.2025.xlsx?rlkey=jk2cdgewfo0o8athpi9t8g0xn&st=2r02zguv&dl=0
The tab labeled "Schmidt" is where the data is being calculated. The cell highlighted yellow are the cells that still need attention. The other tabs are data reference where the formula looks to pull the data from.
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.
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.
