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.
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?
Please explain what is wrong, and what the result should be. It is very difficult to understand what all those formulas referring back and forth are doing.
The formula is trying to answer three questions and return the correct response based on the answer of the question.
Q1 - is the subject male or female
Q2 - is the subject in a combat position
Q3 - is the subject under a profile
If the subject is male, not in a combat role, and does not have a profile, return the values from cells L70:L74
If the subject is male, in a combat role, and does not have a profile, return the values from cells M70:M74
If the subject is male, is either combat or not, and does have a profile, return the values from cells O70:O74
If the subject is female, not in a combat role, and does not have a profile, return the values from cells N70:N74
If the subject is female, in a combat role, and does not have a profile, return the values from cells M70:M74
If the subject is female, is either combat or not, and does have a profile, return the values from cells P70:P74
The scores are pulled into the table above, the formula; "=IF($D$68="YES", "0", IF($G$64="Male", IF($G$65="Yes", IF($G$66="Yes", $O$70, $M$70), IF($G$66="No", $M$70, $L$70)), IF($G$65="Yes", IF($G$66="Yes", $P$70, $M$70), $N$70)))" is referenced in the table below:
It works well for the most part, it is not properly recognizing the profile portion as the AE should show either a 59 or a 60.