vlookup and offset

spectrum225

New Member
Joined
May 26, 2005
Messages
10
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!!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
spectrum225 said:
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

New Member
Joined
May 26, 2005
Messages
10
I don't completely understand it, but that worked perfectly!!
This board is awesome!

Thanks!!!!!
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
spectrum225 said:
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

New Member
Joined
May 26, 2005
Messages
10

ADVERTISEMENT

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

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top