excel table row column lookup

问题:

I'm struggling with some Excel functions.

I have a table with row header and column header and then data. For example: If I have a type of pizza, crossed with its Size, and the data is the price.

               S    M      L    XL
 Cheese        6    6.5    8    10
 Pepperoni     6.5  7.5    10   12
 Supreme       8    9      12   15

If I have this table in excel, how can I do a lookup of the price if I know I want "Cheese", "M"?

The row and colmn headers will be text and not necessarily be in any type of sorted order.

thanks!!

回答1:

Use Index match:

=INDEX(A:E,MATCH("Cheese",A:A,0),MATCH("M",1:1,0))

To call with data in cells so it is dynamic:

Say in F1 you have Cheese and in G1 you have M:

=INDEX(A:E,MATCH(F1,A:A,0),MATCH(G1,1:1,0))

The first part of the Index formula is the Search range. In this case full column A to E.

The second part denotes what row. The match finds the first instance of "Cheese" in column A and returns that row.

The third part denotes the column. That match finds "M" in Row 1 and returns the column number.

Now Index, using those coordinates, knows what to return.

回答2:

Adjust your range references as necessary ($A$2:$E$4 contains the table data, $A$1:$E$1 is your headers - sizes in this case). You can also replace the "Cheese" and "M" with cell references.

=VLOOKUP("Cheese",$A$2:$E$4,Match("M",$A$1:$E$1,0),FALSE)