Offset Vlookup Result

michaeldenna

New Member
Joined
Aug 9, 2009
Messages
10
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

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