return a value in the array at the intersection of row and column name

Question

Dear Sir,

i need help for this issue please :

suppose i have a table in excel

for example :

Class A	Sim-220	Sim-380	Dup-220
a-0.75	1696	1625	2910
a-1.1	1696	1625	2910
a-1.5	1696	1625	2910
a-2.2	1696	1625	2910
a-3	1703	1625	2924
a-4	1733	1625	2985

 i need to return the value witch at the  intersection of row and column name

i.e: a-3 with Dup-220 the value will be 2924

     a-1.1 whit Sim-380 the value will be 1625

HOW can i do this in excel??????

Answer 1

Hi,

Assuming your data as shown above is in A1:D7...

In G1 you could put "Sim-380"      (the column heading to be looked-up)

In G2 you could put "a-1.1"           (the row heading to be looked-up)

Then in G4 you could use    =MATCH(G1,B1:D1,0)   to return the "column number"

And in G5 you could use     =MATCH(G2,A2:A7,0)   to return the "row number"

Then in G6 you could use    =INDEX(B2:D7,G5,G4)  to return the desired value.

You could merge all that into a single formula like this:

=INDEX($B$2:$D$7,MATCH(G2,$A$2:$A$7,0),MATCH(G1,$B$1:$D$1,0))

Hope that helps.

Cheers

Rich

PS. An alternative, which you might find easier to work with since it contains only one MATCH function, thus can be less confusing, is to use a VLOOKUP function:

=VLOOKUP(G2,$A$1:$D$7,MATCH(G1,$A$1:$D$1,0),FALSE)

Answer 2

Let's say your table is in A1:D7, with headers in row 1 and column A.

Enter a value such as a-3 in cell K1

Enter a value such as Dup-220 in cell K2

The formula to retrieve the corresponding value (2924 in this example) is

=INDEX($B$2:$D$7, MATCH(K1, $A$2:$A$7, 0), MATCH(K2, $B$1:$D$1, 0))

If you want to suppress the #N/A error value that is returned when K1 or K2 is blank or contains an invalid entry:

=IFERROR(INDEX($B$2:$D$7, MATCH(K1, $A$2:$A$7, 0), MATCH(K2, $B$1:$D$1, 0)),"")

Answer 3

Hi,

Assuming your table is in A-D Columns, then enter "a-3" (no quotes) in F2 and enter  "Dup-220" (no quotes) in G1 then enter this in G2:

=HLOOKUP(G$1,A:D,MATCH($F2,A:A,0),FALSE)

As your Class A list that you want to look are in F Column, copy now the cell G2 and paste downward.

Hope that helps,

Jaeson