Look up for value of a text field

[XcelLearner]

Hi,

I have a balance sheet simplified like below.

A	B	C	D	E	F	G	H
Year	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7
Cash	44	41	92	225	72	386	362
Cash equivalents	51	0	1	322	40	83	0
Short-term investments

<tbody>
</tbody>

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)