|
lookup and offset formula
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 |
lookup and offset formula
On Wed, 11 May 2011 12:08:08 -0700 (PDT), snax500 wrote:
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) |
| All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com