[![enter image description here][2]][2]
Purpose:
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.
Background:
- 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
TABLE:
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
Already achieved:
- 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)
Assistance required:
- 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][1]The screenshot shows how some calculations give a #N/A result.
Regards. Rudi
4
in yourINDEX(B5:B8, MATCH(J5, E5:E8, 4))
? The third parameter ofMATCH
only ranges from-1, 0, 1
forgreater than, exact match, less than
, not the number of columns.