excelexcel-formula

Excel Lookup Formula not Returning Correctly

I'm looking to use values on another mechanical page that correspond to columns on the table, look in that row for that student and return 1 of 3 values if it is blank or not.

Sample

First page [First Page][1]

Mechanical Values [Mechanical Values][2]

Problem is right now if any columns are not blank it is returning as if they are "Ahead of Schedule", I've looked over this a dozen times and can't figure out where my logic is failing at. Formula used is:

=LET(
units,$B$2:$M$2,
data,$B3:$M3,
behind,'Mechanical (Do Not Modify)'!B13,
on,'Mechanical (Do Not Modify)'!B14,
ahead,'Mechanical (Do Not Modify)'!B15,
idx_behind,MATCH(behind,units,0),
idx_on,MATCH(on,units,0),
idx_ahead,MATCH(ahead,units,0),
cell_behind,INDEX(data,idx_behind),
cell_on,INDEX(data,idx_on),
cell_ahead,INDEX(data,idx_ahead),
IF(NOT(ISBLANK(cell_ahead)),"Ahead of Schedule",
IF(NOT(ISBLANK(cell_on)),"On Schedule",
IF(ISBLANK(cell_behind),"Behind Schedule","Behind Schedule")
)
))

An easier solution would be acceptable, but it needs to dynamically change the column it looks in based on the mechanical value reference. I.E. if ahead of schedule was changed to 3.50 then those currently ahead would fall to on-schedule.

Solution

  • So this solution will require you to point the formula to where it applies in your sheet since things are spread across a few different sheets, but the logic should be sound.

    enter image description here

    So first I want to point out that having the number headers as text will likely cause an issue of their own when the key they're looking against and the headers inevitably have a varying data type. I would urge you to stick to unique names whether they are labels or numbers, but numbers allow better fuzzy-matching (which I'll get into shortly).

    First, the firstblank is counting how many blanks there are, if there's 0 it will explicitly say 0, since a countifs would give a spill range for the output. Then we find the header label in that index and use that in the xlookup to pull in the value for the label. If users update the codes from say 3.1-3.4 to 3.4-3.9 it will adjust accordingly.

    =LET( firstblank, COUNTIFS(B4:L4,"<>"),
header, IF(firstblank=0,0,INDEX($B$3:$L$3,1,firstblank)),
look, XLOOKUP(header,$P$4:$P$6,$O$4:$O$6,"Behind",-1),
IF(firstblank>=COUNTA($B$3:$L$3),"Ahead",look)
)

    The problem you may notice here is that the lower limit should be the lowest limit for example. You'll see in my logic I'm looking for not the exact match, but the closest match or the next lowest match. Meaning if a user is well past 3.4 it will continue to say ahead, since you set the lower limit to 3.4. However, you can see the lowest limit is set to 3.1, so the row that is only at 3.0 has my explicit note "Behind" but if you set the lowest limit to 3, it will say behind until the user passes the 3.2 threshold.

    In the screenshot below you can see I set the lower limit to 0, so until the users hit 3.2 they are "Behind Schedule".
    enter image description here