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))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Renee" wrote in message
...
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.
|