Look up for value of a text field

[XcelLearner]

Hi,

I have a balance sheet simplified like below.

[TABLE="width: 688"]
<tbody>[TR]
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]F[/TD]
[TD="align: right"]G[/TD]
[TD="align: right"]H[/TD]
[/TR]
[TR]
[TD] Year[/TD]
[TD="align: right"]Year 1[/TD]
[TD="align: right"]Year 2[/TD]
[TD="align: right"]Year 3[/TD]
[TD="align: right"]Year 4[/TD]
[TD="align: right"]Year 5[/TD]
[TD="align: right"]Year 6[/TD]
[TD="align: right"]Year 7[/TD]
[/TR]
[TR]
[TD] Cash[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]386[/TD]
[TD="align: right"]362[/TD]
[/TR]
[TR]
[TD]Cash equivalents[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] Short-term investments[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I would like to look for value of "Cash" in year 1, and expect to have the value as "44".
I tried the formula VLOOKUP("Cash",A1:H5,2,0) without any success. Could you please give me a solution for this problem? Thank you very much.

Nam

 

[mole999]

yours should work, in J1 > =VLOOKUP(I1,A1:H5,2,0) cash is placed in I1 so that you don't have to keep changing the formula

 

[Aladin Akyurek]

Try...

=VLOOKUP("cash",A:H,MATCH("year 1",INDEX(A:H,1,0),0),0)

 

[XcelLearner]

Thank you very much for your replying, Mole and Aladin. Mine actually works, after hours of scratching my head, I recognized the data provider added several spaces before Cash (in effect to make it " Cash", not "Cash"), that caused me headaches.

Thanks a lot again.

 

[Aladin Akyurek]

Thank you very much for your replying, Mole and Aladin. Mine actually works, after hours of scratching my head, I recognized the data provider added several spaces before Cash (in effect to make it " Cash", not "Cash"), that caused me headaches.

Thanks a lot again.

Yours work only for year 1.

If you use cells for the look up values, say, X1 = cash and Y1 = year 1, you can have in Z1 just...

=VLOOKUP(X1,A:H,MATCH(Y1,INDEX(A:H,1,0),0),0)