Formula in Excell

N

naflan

I want to refer a value in a table using a formula where the value will
change according to column heading row heading
 
R

Ragdyer

If I understand what you're looking for,

Column labels in B1:K1
Row labels in A2:A5
Data in B2:K5

Enter row label to find in L1,
Enter column label to find in L2.

If datalist in B2:K5 is numbers only, try:
=SUMPRODUCT((A2:A5=L1)*(B1:K1=L2)*B2:K5)

If datalist contains text and/or numbers, try:
=INDEX(B2:K5,MATCH(L1,A2:A5,0),MATCH(L2,B1:K1,0))
 
R

Ron Coderre

Without more information, I'm guessing what you're looking for...so...
Try this example:

Put this information in cells A1:D4

FirstCol SecondCol ThirdCol
FirstRow 11 12 13
SecondRow 21 22 23
ThirdRow 31 32 33

F1: SecondRow
F2: SecondCol
F3: =INDEX(B2:D4,MATCH(F1,A2:A4,0),MATCH(F2,B1:D1,0))

That formula looks in the range B2:D4 and finds the intersection of the row
heading referenced in F1 and the column heading referenced in F2. In this
case, it returns 22.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top