lookup and offset formula

[snax500]

In Excel2007, I need a formula that will lookup an item in another file, find it and then give me the cell contents 3 rows up and 1 over to the right. For example...

file 1...

today 50
yesterday 100
tomorrow 200
xyz

today 20
yesterday 200
tomorrow 400
abc


file 2...
formula to lookup "xyz" and then give me today's amount = 50. the captions are in one column and the amounts in the next column. Thanks

 

[Ron Rosenfeld]

In Excel2007, I need a formula that will lookup an item in another file, find it and then give me the cell contents 3 rows up and 1 over to the right. For example...

file 1...

today 50
yesterday 100
tomorrow 200
xyz

today 20
yesterday 200
tomorrow 400
abc


file 2...
formula to lookup "xyz" and then give me today's amount = 50. the captions are in one column and the amounts in the next column. Thanks

You would use a combination of INDEX and MATCH.

e.g.

Your table is in File 1 Sheet2 cells A1:B9

With "xyz" in File 2 Sheet1 A1:

B1: =INDEX([File1]Sheet2!$A$1:$B$9,MATCH(A1,[File1]Sheet2!$A$1:$A$9,0)-3,2)