MrExcel Publishing
Your One Stop for Excel Tips & Solutions

lookup & offset in same function with multiple files


Posted by Tracy T on November 13, 2001 10:46 PM

I need to lookup a cell from File A in the first row of a tab in file B, then reference cells 6 rows below and 47 columns over from the "looked up" cell in file B. I need the offset value to appear in the function cell.

So...Look up the value "x" from file A in the first column of file B, then go down 6 rows and over 47 columns and retrieve the value in that cell.

Can this be done?


Posted by Juan Pablo on November 14, 2001 6:11 AM

Don't know if this works, but give it a shot.

=OFFSET([BookB.xls]Sheet1!$A$1,6,MATCH([BookA.xls]Sheet1!$B$5,[BookB.xls]Sheet1!$1:$1,0)+46)

Juan Pablo