Look Up help please

T

Tony

In a spreadsheet as exampled below how can I get Excel to look up a
particular reference and use that in a calculation?

e.g.
A B C
X 4 6 7
Y 2 3 6
Z 7 4 9

So if I had entered in one cell YB it would use the value at their
intersection (3 in this case) in further calculations?

Hope someone can help. Thanks.

Tony
 
S

Sloth

That is a rather confusing question, because excel rows are labeled with
numbers. So your example would be B2 not YB. If you wanted to do a
calculation using the value of cell B2 as a variable, then it would be like
this:

=B2+8-B2*2

The result would be 7. The equal sign tells excel that you are using a
formula, and outputs a result instead of what you type.
 
E

Eric

Tony,

A combination of MATCH and INDEX should do the job. Assuming your "X,Y,Z"
is in cells A2-A4 and your "A,B,C" is in cells B1-D1:

=INDEX( B2:D4, MATCH("Y",A2:A4,0), MATCH("B",B1:D1,0) )

Replace the "Y" and "B" with references to cells if you want.

Hope this helps.

Eric
 
L

L. Howard Kittle

Hi Tony,

Try this formula modified from a Google search of INDEX MATCH.

=INDEX(B2:D4,MATCH(LEFT(H1,1),A2:A4,0),MATCH(RIGHT(H1,1),B1:D1,0))

Where:
B1:D1 = A B C
A2:A4 = X Y Z
B2:D4 = The numbers
H1 = YB returns 3, ZA returns 7, etc.

HTH
Regards,
Howard
 
R

RagDyeR

If you *actually* have a data list set up as you posted, where the cells are
populated:
A1 = empty
B1 = A
C1 = B
D1 = CC - (for some reason "C" is reserved)

AND

A2 = X
A3 - Y
A4 = Z

*AND*
<Tools> <Options> <Calculation> tab,
"Accept Labels In Formulas"
*IS* checked,

THEN
=Y B
*WILL* return a 3

This is called the "intersection operator" and is simply a <Space>.
(Notice <space> between Y & B)

So that:
=Y B*Z CC
Will return 27

Just as:
=B Y+CC Z
Will return 12

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



In a spreadsheet as exampled below how can I get Excel to look up a
particular reference and use that in a calculation?

e.g.
A B C
X 4 6 7
Y 2 3 6
Z 7 4 9

So if I had entered in one cell YB it would use the value at their
intersection (3 in this case) in further calculations?

Hope someone can help. Thanks.

Tony
 
Top