# Offset Vlookup Result

#### michaeldenna

##### New Member
hi guys,

thank you in advance for helping me with my query.

in short I've almost finished my spreadsheet yet I'm stuck on one point.

I created a drop down with 60 different options to choose from. I want to be able to vlookup the selected option from the drop down and retrieve up to 30 results based on that drop down.

e.g a1 is the selected result from the dropdown
=vlookup(a1,a5:b65,2,0).

because there are 30 different bits of info that i want to retrieve as a result of selected the option from the drop down, i figuire i would have the 30 different results sequentially below and offset the result

e.g =vlookup(a1,b5:65,2,0) - cell 1
=offset(vlookup(a1,b5:65,2,0),0,-1) - cell 2
etc,etc for the 30 cells.

I'm in desperate need of help as Im due to hand in this report sometime today.

thanks
michael

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### AlphaFrog

##### MrExcel MVP
Are the 29 other bits in the cells below the initial match from the Vlookup? Where are the 29 other bits relative to the match?

It's not clear from your offset formula. It seems to offset one column to the left for each bit. If you start at column B, then you quickly run out of offsets to the left.

#### michaeldenna

##### New Member
Are the 29 other bits in the cells below the initial match from the Vlookup?
Yes

Where are the 29 other bits relative to the match?
Yes. The drop down list is a list of departments and the 30 cells are teams within the departments. essentially i'm vlookuping the department code and returning 30 teams but each cell should offset the row by 1.

It's not clear from your offset formula. It seems to offset one column to the left for each bit. If you start at column B, then you quickly run out of offsets to the left.

Apologies it should be offsetting the row not column
ie =offset(vlookup(a1,b5:65,2,0), -1,0)

i hope that makes better sense?

#### AlphaFrog

##### MrExcel MVP
C1 has your drop down list

Search A1:A65 for the value from C1
Return the Match from column B
Offset to the next row down from the match as the formula is copied down

=INDEX(\$B\$1:\$B\$65, MATCH(\$C\$1, \$A\$1:\$A\$65,0)+ROW(A1)-1)

The part in red is the offset for each row.

INDEX - MATCH Functions

Last edited:

#### Dryver14

##### Well-known Member
so if the vlookup returned B7 col 2
you would wnat B8 col 2
B9 col 2 etc for 30 rows?

#### michaeldenna

##### New Member
C1 has your drop down list

Search A1:A65 for the value from C1
Return the Match from column B
Offset to the next row down from the match as the formula is copied down

=INDEX(\$B\$1:\$B\$65, MATCH(\$C\$1, \$A\$1:\$A\$65,0)+ROW(A1)-1)

The part in red is the offset for each row.

INDEX - MATCH Functions

Alphafrog
thank you heaps for this. this worked a TREAT!!!!

Replies
3
Views
97
Replies
3
Views
557
Replies
6
Views
370
Replies
1
Views
167
Replies
1
Views
258

1,190,564
Messages
5,981,704
Members
439,731
Latest member
auraitsuka

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