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
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty