Offset Vlookup Result

[michaeldenna]

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

 

[AlphaFrog]

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]

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]

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

 

[Dryver14]

so if the vlookup returned B7 col 2
you would wnat B8 col 2
B9 col 2 etc for 30 rows?

 

[michaeldenna]

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