# vlookup and offset

#### spectrum225

##### New Member
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.

### 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.

Sure, use INDEX/MATCH

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

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

Thanks!!!!!

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?

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

=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.

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

Replies
11
Views
263
Replies
11
Views
330
Replies
1
Views
164
Replies
4
Views
153
Replies
1
Views
308

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.

### Which adblocker are you using?

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

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