2

[![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:

  1. Some values in table body are repeated in different columns
  2. The values in the table body represent lower limits of intervals
  3. The column headers in C4 to F4 represent lower limits of intervals
  4. 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:

  1. The information in J4 & J5 is always given (i.e. known)
  2. HLOOPKUP (J4) is used to identify the correct column header - and the correct result appears in K4, i.e. column E
  3. 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:

  1. Currently I first have to run the HLOOKUP formula to determine the column header
  2. 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
  3. So, I need assistance to automatically insert the column values (excluding the column header) based on the HLOOKUP result into the INDEX/MATCH formula.
  4. 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

  • I think instead of the Hlookup use an index match, which would give you the column index and not the column "label". You should be able to use a formula to convert that to the column index in your next index function. – gns100 Jun 7 '17 at 18:18
  • Why you have 4 in your INDEX(B5:B8, MATCH(J5, E5:E8, 4)) ? The third parameter of MATCH only ranges from -1, 0, 1 for greater than, exact match, less than, not the number of columns. – Vylix Jun 7 '17 at 21:10
0

Use OFFSET to shift the range from the upper leftmost cell to get the correct range.

=INDEX($B$5:$B$8, MATCH($J$5, OFFSET($C$5, 0, MATCH($J$4, $C$4:$F$4, 1)-1, ROWS($B$5:$B$8), 1), 1))

Explanation

This behaves just like your INDEX MATCH formula, however I changed the E5:E8 in your example to a relative range, defined by OFFSET( ... )

OFFSET($C$5, //Reference point
       0,    //Rows shifted below, in this case it doesn't shift the row
       MATCH($J$4, $C$4:$F$4, 1)-1, //Column shifted to the right, in this case it shifts from `1-4` minus 1.
       ROWS($B$5:$B$8),           //Height, in this case you want as many rows as the table has, taken from the left "header"
       1)    //Width, in this case you only want one column

$C$5 is the upper leftmost cell on your values that is used as a reference point.

MATCH($J$4, $C$4:$F$4, 1)-1 is to get the index of the column header. This is similar to your HLOOKUP formula, but instead it returns the column number (3rd column returns 2, for example)

ROWS($B$5:$B$8) is to get how many rows the table has, taken from how many rows the left "header" has

  • Thanks for speedy response Vylix. I have inserted your suggestions and table already shows a vast improvement. However, there are still some challenges (see table) : Length: 4.1 5 5.5 6 Strength 18 21 26 31 Result #N/A 7 #N/A #N/A Expected result 7 7 9 16 Any suggestions? – R Burger Jun 8 '17 at 13:37
  • Can you upload a screenshot in the question? – Vylix Jun 8 '17 at 14:56
  • Excuse my ignorance - but how do I do that? (read help re. Comments but did not find an answer) – R Burger Jun 8 '17 at 18:52
  • I know how to make a screenshot but not how to attach to comment. – R Burger Jun 8 '17 at 18:53
  • @RBurger edit your question, and add with the uploaded screenshot. Just provide the link, we can make it shown in the question. – Vylix Jun 8 '17 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.