Vlookup formula with Offset

[bill_s1416]

Sheet A ('Customers Alignment'):
--------------------------------------
column A- name of sales representative
column B - customer ID
(Not sorted in any particular order. So, a sales rep could be listed in row 5, 27, etc)

Sheet B ('Sales Rep Lists'):
------------------------------
cell 'A1' drop-down selection of sales rep name.
I need below that to list the customers of the sales rep selected in cell A1 in a continuous list, even though the data is randomly sorted on sheet A. Can this be done with a single formula (maybe with an Vlookup and/or an Offset?)

 

[Domenic]

Try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

'Sales Rep Lists'!B1, copied down:

=IF(ROWS(B$1:B1)<=COUNTIF('Customers Alignment'!$A$1:$A$100,$A$1),INDEX('Customers Alignment'!$B$1:$B$100,SMALL(IF('Customers Alignment'!$A$1:$A$100=$A$1,ROW('Customers Alignment'!$A$1:$A$100)-ROW('Customers Alignment'!$A$1)+1),ROWS(B$1:B1))),"")

Adjust the ranges accordingly.

Hope this helps!

 

[Peter_SSs]

Sheet A ('Customers Alignment'):
--------------------------------------
column A- name of sales representative
column B - customer ID
(Not sorted in any particular order. So, a sales rep could be listed in row 5, 27, etc)

Sheet B ('Sales Rep Lists'):
------------------------------
cell 'A1' drop-down selection of sales rep name.
I need below that to list the customers of the sales rep selected in cell A1 in a continuous list, even though the data is randomly sorted on sheet A. Can this be done with a single formula (maybe with an Vlookup and/or an Offset?)

Could you just use AutoFilter on the data in the original 'Customers Alignment' sheet? Using the AutoFilter on column A would give you the list.