Search code examples
arraysexcel-formulalookupsumproduct

How do I lookup row labels, find a value in an array on that line, and report that specific column header?

enter image description here See image of my simple table in excel for reference

  • I have a sporting event that a person is assigned a division: Which is the value in H3.
  • Lookup this division in the table A2:A8. In this example it would be row 5
  • Parse the table row 5 from left to right, comparing the time the person ran this event (value 20.52 sec in H4) and compare to each value in row 5 (19.07... -> 36.98), and find first value that H4 is less than or equal to and report the column header to get an assigned classification. In this example this person would be classified as a EX from column C header label. If the time ran isn't found and exceeds the value in column F that person would be classified as NV from column F by default.

I've tried many different approaches to this using SUMPRODUCT, MATCH, INDEX, CHOOSEROW, etc in various combos and can't get it.

This is the last solid output I was able to generate: lookup division and pull the array of value from row 5 (B5:F5

=CHOOSEROWS(B2:F8,XMATCH($H$3,$A$2:$A$8,0)) this produces the array of values of B5:F5

but from there I can't get get any solid output.

Any and all help would be appreciated.

Solution

  • Could you see whether this would work, enter in H5 for classification:

    Without LET:

    =XLOOKUP(H4, XLOOKUP(H3, A1:A8, B1:F8), B1:F1, F1, 1)

    Preferred:

    =LET(
    data, A1:F8, div, H3, time_ran, H4,
    div_row, XLOOKUP(div, INDEX(data, , 1), DROP(data, , 1)),
    XLOOKUP(time_ran, div_row, DROP(TAKE(data, 1), , 1), TAKE(data, 1, -1), 1)
)

    DROP function - Microsoft Support

    Result