It's not your formula that's failing you, it's the mismatch between your data and the reference tables. You're trying to look up exact matches across sex and age, but the CDC's LMS parameters don't align with the patient data you've structured. The `XLOOKUP` with two conditions is a solid approach, but it assumes the lookup values in both sheets are identical in format and precision. A mismatch in how age is recorded (years vs. months, decimal precision, or data type) will return `#N/A` every time. The same applies to the sex column if one sheet uses "M/F" and the other uses "Male/Female" or numeric codes. You're not winging it badly, you're just missing the alignment step that documentation rarely explains.
The practical fix is to normalize both datasets before the lookup. Convert your patient ages into the exact format the CDC uses (typically completed months or half-months for growth charts). Standardize the sex column to match exactly. Then, instead of an exact match, use a range-based lookup or a helper column that concatenates sex and age into a single key. A `XLOOKUP` with concatenation, `=XLOOKUP(A2&"_"&B2, CDC_LMS!A:A&"_"&CDC_LMS!B:B, CDC_LMS!C:C)`, often resolves the issue because it eliminates the multiple-condition headache. If that still fails, check for trailing spaces or hidden characters by using `TRIM()` and `CLEAN()` on the lookup arrays. These invisible gremlins are the top reason cross-sheet lookups break.
Once you pull the correct L, M, and S parameters, calculating the z-score is a straightforward formula: `=((Patient_BMI/M)^L - 1)/(L*S)`. Then convert that z-score to a percentile using `=NORM.S.DIST(z_score, TRUE)`. The entire pipeline, lookup, z-score, percentile, can be done in three columns and copied down for all 400 patients. No manual math, no guesswork, just a repeatable process that works for any pediatric dataset.
This is exactly the kind of task where a traditional spreadsheet forces you to become a data janitor before you can be an analyst. The CDC provides the raw materials, the LMS parameters, but leaves the assembly to you. That's not a failure of your effort; it's a failure of the tool to meet you where you are. A spreadsheet that understood your intent would let you say "give me the CDC percentile for this patient" without building a lookup system from scratch. Until then, the solution is methodical data prep and a formula chain that turns a frustrating error into a finished result.