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.
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.
Mr. HansVogelaar,
My apologies, I submitted this last week but it did not go through. Attached is the spreadsheet I have been working in. The tab labeled "Schmidt" is the tab where the data is being pulled into, the reference tabs with the data are all labeled. The cells highlighted yellow are the formulas I am having issues with.
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! I knew it was more than likely a formatting error but could not identify it. I do have another issue, which I think is an order of operations in the formula. In columns O and P rows 82 - 87 I have the following formula - =IF($G$64="Male", IF($G$65="Yes", IF($G$66="Yes", $M$69, $L$69), IF($G$66="Yes", $O$69, $M$69)), IF($G$65="Yes", IF($G$66="Yes", $P$69, $M$69), $N69)).
The idea is to look at the gender, if they are in a combat role, and if they have a profile. The formula is not returning the correct values when I change the parameters, ex. Female, non-combat, profile; female, combat, profile; male, combat, no profile, etc and have it return the correct score from O82/P82, respectively to cell H74.
Would you mind taking a look at that?