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!!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,216,309
Messages
6,130,001
Members
449,551
Latest member
MJS_53

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
Back
Top