Table Lookup in Excel

Question

I am very familiar with vlookup and hlookup functions in Excel. However, I am looking for a method of doing both. Take this example:

        A             B        C  
1                     Resources
2   Task            Mgr   Sr. Mgr
3   -----------------------------
4   Task 1          30%       70%
5   Task 2          40%       60%
6   Task 3          50%       50%
7   Task 4          70%       30%

If I wanted to put a formula in a new cell to look up both a task and a resource type to return the appropriate percentage, how could I do this?

Please let me know if more clarification is needed.

Answer 1

=OFFSET(A3,MATCH("Task 3", A4:A7, 0),MATCH("Mgr",B2:C2,0))

Of course, you're probably getting the things to look for from other cells, so replace "Task 3" and "Mgr" above with references to those cells.

Answer 2

A combination of INDEX and MATCH will do the trick:

=INDEX($B$4:$C$7,MATCH("Task 3",$A$4:$A$7,0),MATCH("Mgr",$B$2:$C$2,0))

Answer 3

Okay, assume you have an Excel sheet with the following format where your lookup table occupies the cell range A1:E5

     C1    C2    C3    C4
R1  R1C1  R1C2  R1C3  R1C4
R2  R2C1  R2C2  R2C3  R2C4
R3  R3C1  R3C2  R3C3  R3C4
R4  R4C1  R4C2  R4C3  R4C4

Also assume you want to enter the row header name and column header name into cells G3 and H3 respectively (which I have the text values "R3" and "C2").

In the cell you wish to display your output value, you could either use HLOOKUP like so:

=HLOOKUP(H3,A1:E5,MATCH(G3,A1:A5,0))

or VLOOKUP like so:

=VLOOKUP(G3,A1:E5,MATCH(H3,A1:E1,0))

Either displays the value "R3C2" in my output cell.

Answer 4

Another possibility:

=VLOOKUP(E3,A2:C7,MATCH(E2,A2:C2,0),FALSE)

Where

• E3 contains the task to look up
• E2 contains the header column name (eg Mgr)
• A2:A7 is the table of data
• A2:C2 is the header