How do I write formula to compare two values and pull the resulta.

[Renee]

How do I write this formula? I want to try and simplify this inquiry as much
as possible because once I understand how to write the formula, I can run
with it. But I want to take a value from one cell and a value from another
cell and use them to cross reference in a table and pull up the resultant.
For example, In one worksheet Cell A1 has a 4, and Cell A2 has a D, and then
on another worksheet in the same workbook, I have a table that is numbered
down the left side 1 to 10 and across the top A-Z and all those cells in that
table have different values in each one. The question is, how do I write a
formula that takes the 4 in Cell A1 and the D in Cell A2 and cross reference
them in the second worksheet to bring me the value in Cell 4,D. I hope this
makes sense. Thanks.

 

[Max]

One way:

Assume you have the reference grid in Sheet2, where
Listed across in B1:AA1 are : A, B, C, ... Z
Listed down in A2:A11 are : 1,2,3 .. 10

In Sheet1:
-------------
If you have in A1: 4, in A2: D

then you could put in say, A3

=OFFSET(Sheet2!$A$1,MATCH(A$1,Sheet2!$A:$A,0)-1,MATCH(A$2,Sheet2!$1:$1,0)-1)

which will retrieve the contents in "D - 4" in Sheet2

(Copy A3 across to retrieve similarly for other paired inputs in B1 and B2,
C1 and C2, etc)

The row param: MATCH(A$1,Sheet2!$A:$A,0)-1
will match the value in A1 to get the required row in the ref grid

while the col param: MATCH(A$2,Sheet2!$1:$1,0)-1
will match the value in A2 to get the required col in the ref grid

(The "-1" in both row/col params above is an arithmetic adjustment)

And perhaps better with an error-trap to return blanks: "" instead of #NAs,
put instead in A3, and copy across:

=IF(OR(ISNA(MATCH(A$1,Sheet2!$A:$A,0)),ISNA(MATCH(A$2,Sheet2!$1:$1,0))),"",O
FFSET(Sheet2!$A$1,MATCH(A$1,Sheet2!$A:$A,0)-1,MATCH(A$2,Sheet2!$1:$1,0)-1))