vlookup and offset

[spectrum225]

Does anyone know if there is a way to incorporate offset and vlookup in a formula? This is the formula I tried:

=OFFSET(VLOOKUP(sheet1!A1,'otherfile.xls'!A:A,1,FALSE),1,1)

"sheet1!A1" is text in "otherfile.xls" column A

I'm trying to find text in column A in "otherfile.xls" then offset 1 row and 1 column and return that value. Is there another way to do this? I don't really want to use a macro.
Thank you for your help!!

 

[just_jon]

Does anyone know if there is a way to incorporate offset and vlookup in a formula? This is the formula I tried:

=OFFSET(VLOOKUP(sheet1!A1,'otherfile.xls'!A:A,1,FALSE),1,1)

"sheet1!A1" is text in "otherfile.xls" column A

I'm trying to find text in column A in "otherfile.xls" then offset 1 row and 1 column and return that value. Is there another way to do this? I don't really want to use a macro.
Thank you for your help!!

Sure, use INDEX/MATCH

=INDEX([OTHERFILE.xls]Sheet1!$B:$B,MATCH(Sheet1!A1,[OTHERFILE.xls]Sheet1!$A:$A,0)+1,0)

 

[spectrum225]

I don't completely understand it, but that worked perfectly!!
This board is awesome!

Thanks!!!!!

 

[just_jon]

I don't completely understand it, but that worked perfectly!!
This board is awesome!

Thanks!!!!!

Do you want to know how the formula works?

 

[spectrum225]

Yes, if you don't mind. I understand "match", but not "index". The more knowledge I have the better!

 

[just_jon]

=INDEX([OTHERFILE.xls]Sheet1!$B:$B,MATCH(Sheet1!A1,[OTHERFILE.xls]Sheet1!$A:$A,0)+1,0)

The structure of the array version of the INDEX function is:

=INDEX(Targt Array Ref, Row Number, Column Number)

The structure of the MATCH function is:

=MATCH(Lookup What, Lookup Array, Match Type)

In the above formula:

you are using column B of Sheet1 in OTHERFILE as the range from which to return a value.

For its row reference you will use the index number returned by MATCHing the value of Sheet1 cell A1 in the OTHERFILE's column A, plus one -- as you are referencing all of column A, this will return the row number of the A1-in-column A match, plus 1. The last bit -- ,0 -- means there is to use return exact matches only.

The 3rd argument to INDEX is ,0 and it means there is no column offset - simply return from B.

The help file contains more info, search on INDEX and MATCH.

 

[spectrum225]

OK, that makes total sense!!
Thanks again, and thanks for the explanation!