Tjyylli

I have spreadsheet that has a table that is similar in structure to this:

      | 1-2 | 3-4 | 5-6 | 7+

-------------------------------

18-20 |  7  |  2  |  9  |  4  |

21-25 |  1  |  0  |  6  |  3  |

26-30 |  1  |  4  |  6  |  9  |

31+   |  3  |  8  |  5  |  0  |

So say for example, the inputs I got from the user were 2, 19, the lookup should return 7. Or if the inputs were 9, 29, the lookup should return 9.

I know you can do two dimensional lookups when you just have single values in the row and column headers by combining VLOOKUP and MATCH. But how do I do the lookup when the headers are a range of values?

Do I need to just write a custom VBA function or does Excel have some built-in way to do this?

INDEX() can do a two-dimensional lookup. The syntax is INDEX(array,row_num,[col_num],[area_num]). It takes a 2D array and returns the intersection of row_num and column_num.

Your problem is a bit tricky because of the ranges in the headers, but some fast footwork with TRUNC() and IF() does the job. This formula filled down from D8 in the screenshot below, gives the results shown:

=INDEX(B$2:E$5,IF(C8>31,4,TRUNC((C8-11)/5)),IF(B8>7,4,TRUNC((B8+1)/2)))

If you want to see how the TRUNC() portion works, put =TRUNC((ROW()-11)/5) in row 18 or TRUNC((ROW()+1)/2) in row 1 and fill down.

I hope this helps, and good luck.

Given that you are looking in ranges that are mutually exclusive and complete (at least for values >=18), your range can be defined by the first value in each cell. For example, the table you provide can be described equivalently by:

You can then find your value by combining vlookup and match. For example if your table and lookup values were arranged like the following picture:

Your desired value would be in the highlighted cell which would contain the following: =VLOOKUP(B$8,$A$1:$E$5, MATCH($A8,$A$1:$E$1,1),TRUE)

Since the labels are well-formed, which in the case of labels means, basically, they are ordered as if they had been sorted low-to-high by their first characters' character codes, one can take advantage of the last argument in VLOOKUP() and HLOOKUP(), taking the "approximate match" choice.

You do this to create the lookup value and the column to look in for a VLOOKUP() which will do the final work.

In the end, you have a simple VLOOKUP(). What to look up in it? Let's say your table shown is A1:E5 with its row labels in A2:A5 (DO NOT include that first row!) and its column labels being in B1:E1 (DO NOT include that first column!) and its actual data in B2:E5. If you include those first rows and columns (A1's), you will get the #N/A error. And let's say you handle obtaining the input values yourself, so however the "2, 19" material is created is however it is made available (I'll call it "Column to look in" and "Row to look in".) Then:

=HLOOKUP( "Row to look in" , A1:A5 , 1, TRUE )

will return "18-20" which we will use directly as the lookup value, and

=VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE )

will return "1-2" and here is where the "slight trick" comes in.

If we wrap that last function in a MATCH() function, it uses the "1-2" to find the column number to look in which the final VLOOKUP() requires. (An anonymous person on Excel's UserVoice site showed me this.)

You must now use the range we just avoided, A1:E1, to get the column number directly. If you begin it with B1, you have to add a "1" to the result, so this is simpler to read and understand:

=MATCH(  VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE )  , A1:E1 )

which returns "2" for the column (coincidentally, nothing more, the same as our input, but admit no confusion into your game!). So finally, you will put it all together as:

=VLOOKUP( HLOOKUP( "Row to look in" , A1:A5 , 1, TRUE ) , A1:E5 , MATCH( VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE ) , A1:E1 ) , FALSE )

Use "FALSE" for it because you either have exact matches available, or something has gone BADLY wrong.

(If only the columns, or only the rows are amenable, you can still adapt here. You just have a harder time because only half of it is solved with this, but that can be enough, I have found. I've used it maybe 6-8 times since I learned the MATCH() idea to regain the row/column headers as Labels functionality and half of them only one was "ruly".)