return store assoc. for selected store #

[btimp1]

this is my first post, so please be gentle!

i need a formula to work the following scenario.

On sheet 1, i want cell a1 to be store 403 or 516. based on this criteria, i need cell b1:b10 (or however many cells are needed) to fill in with employee names from raw data on sheet 2. the data on sheet 2 is arranged with employees in col. a and stores in col. b

Employee Store

RICHARDSON, JIM	403
HO, CANH	403
KOTTKE, JOSHUA	406
MAROTTA, FRANK	407
VERNACCHIO, STEPHEN	465
YOUNGBLOOD, DAVID	516
CONNER, CHASE	516

<tbody>
</tbody>

please let me know if you need more info or clarity

 

[cdchapman]

Assuming the store # is in cell A1 on Sheet1 either by entering it manually, or via a formula, then try the following formula in cell B1 on Sheet 1:

=IFERROR(IF(ROWS(B$1:B1)>COUNTIF(Sheet2!$B$1:$B$7,Sheet1!$A$1),"",INDEX(Sheet2!$A$1:$A$7,SMALL(IF(Sheet2!$B$1:$B$7=Sheet1!$A$1,ROW($A$1:$A$7),""),ROWS(B$1:B1)))),"")

This formula has been based on your example data and needs to be entered with CTRL+SHIFT+ENTER, not just ENTER as it is an array formula. If necessary change the Sheet2 ranges to match your actual Sheet 2 data. Copy the formula down for as many rows as necessary.

It should list all employee names with a store number that matches the value in Sheet1 cell A1.

Thanks

Chris

 

[mancubus]

welcome to MrExcel.

check this:
Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value

 

[brucef2112]

this is my first post, so please be gentle!i need a formula to work the following scenario.
On sheet 1, i want cell a1 to be store 403 or 516. based on this criteria, i need cell b1:b10 (or however many cells are needed) to fill in with employee names from raw data on sheet 2. the data on sheet 2 is arranged with employees in col. a and stores in col. b
Employee Store
< table of data >
please let me know if you need more info or clarity

Have you considered just filtering the raw data with Excel's AutoFilter?
Select all the data on the worksheet including the column headers.
On the menu bar select Data > Filter > AutoFilter.
You'll now see a small button in each cell's header in row 1.
click the button on your Store Number column and select the store you need.
Rows will be filtered to those employees with that store number.

Want to see the employees for store 406 AND 516.
Select 'Custom...' from the column header and enter your store numbers and select the 'OR' choice.

If you have other column data (like shoe size) you can further filter by making filter selections in them also.

so store 516 and employee with size 9 shoes.

 

[btimp1]

.

 

[btimp1]

Assuming the store # is in cell A1 on Sheet1 either by entering it manually, or via a formula, then try the following formula in cell B1 on Sheet 1:

=IFERROR(IF(ROWS(B$1:B1)>COUNTIF(Sheet2!$B$1:$B$7,Sheet1!$A$1),"",INDEX(Sheet2!$A$1:$A$7,SMALL(IF(Sheet2!$B$1:$B$7=Sheet1!$A$1,ROW($A$1:$A$7),""),ROWS(B$1:B1)))),"")

This formula has been based on your example data and needs to be entered with CTRL+SHIFT+ENTER, not just ENTER as it is an array formula. If necessary change the Sheet2 ranges to match your actual Sheet 2 data. Copy the formula down for as many rows as necessary.

It should list all employee names with a store number that matches the value in Sheet1 cell A1.

Thanks

Chris

i can't seem to get this one to work, even with ctrl+shift+enter...


also, filtering will not work as this is only a small part of the end result needed

 

[btimp1]

welcome to MrExcel.

check this:
Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value

i finally got it!!! this was my basis and i modded to fit my needs. thanks!