[![enter image description here]]
Use a known "length" value to find correct column header, then search for a value in the column, then return index on the right hand of that data table.
- Some values in table body are repeated in different columns
- The values in the table body represent lower limits of intervals
- The column headers in C4 to F4 represent lower limits of intervals
- The row headers in B5 to B8 each represent a unique index
A B C D E F G H I J K 3 Length Header 4 **4.1 5 5.3 6** Length: 5.4 5.3 =HLOOKUP($J$4;C4:F4;1) 5 **7** 18 20 21 23 Strength 28 9 **20** 22 23 25 Index 11 =INDEX(B5:B8;MATCH(J5;E5:E8;4)) 7 **11** 24 25 27 28 8 **16** 27 28 30 31
- The information in J4 & J5 is always given (i.e. known)
- HLOOPKUP (J4) is used to identify the correct column header - and the correct result appears in K4, i.e. column E
- The (INDEX/MATCH) formula in J6 then correctly searches the values under the column E header (E5:E8 - which I have to type in) -
and correctly identifies that "28" (J5) falls in the "27, " interval (E7) - and then correctly identifies the corresponding row header as "11" (B7)
- Currently I first have to run the HLOOKUP formula to determine the column header
- Next I have to manually type in the column values (excluding the column header) - based on the result of K4, into the formula in J6 in order to run the INDEX/MATCH function
- So, I need assistance to automatically insert the column values (excluding the column header) based on the HLOOKUP result into the INDEX/MATCH formula.
I would prefer using functions rather than VBA if at all possible
[Screenshot of errors]The screenshot shows how some calculations give a #N/A result.