| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup, then offset? | Excel Worksheet Functions | |||
| Lookup with offset | Excel Worksheet Functions | |||
| Lookup and offset | Excel Discussion (Misc queries) | |||
| Lookup and offset | Excel Discussion (Misc queries) | |||
| lookup with offset? | Excel Worksheet Functions | |||
Linear Mode
