Extract a list of names in Excel based on a list of store numbers

Question

I have a list of names in column A with no repetitions. In column B I have a list of store numbers with multiple repetitions, usually 7-8 names per store number. On another worksheet I have a list of the stores I'd like to reference, as I don't want all of the stores or names to feed into my report. I'd like to create a formula that says look for these particular store numbers and give me all the names associated with the store numbers. Any suggestions?

Answer 1

Hi eagleruler2,

I have a list of names in column A with no repetitions. In column B I have a list of store numbers with multiple repetitions, usually 7-8 names per store number. On another worksheet I have a list of the stores I'd like to reference, as I don't want all of the stores or names to feed into my report. I'd like to create a formula that says look for these particular store numbers and give me all the names associated with the store numbers. Any suggestions?

Select the store list and create a defined name  (Ctrl+F3) for the list - say StoreList.

In cell C2of the first sheet enter the formula 

              =VLOOKUP(B2,StoreList,2,0)

Drag/Copy the formula down as far as needed:

You can download my test file EagleRuler20160408.xlsx at:

https://www.dropbox.com/s/rcikrg0dml8y0me/EagleRuler20160408.xlsx?dl=0

===

Regards,

Norman

Answer 2

Refer below image:

Column A has a list of names with no repetitions.

Column B has a list of store numbers with multiple repetitions.

In column E we return the store names (from column A) corresponding to the store number in cell D2. Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell E2 & copy down:

=IFERROR(INDEX($A$2:$A$15,SMALL(IF($B$2:$B$15=D$2,ROW($B$2:$B$15)-ROW($B$2)+1),ROWS($B$2:$B2))),"")

You may change store number in cell D2, and adjust range in the formula per your requirement.

Regards,

Amit Tandon

Answer 3

I've tried the INDEX SMALL option before, but run into an issue because the logical test requires it to look up a variety of options, not just one. Also, my source spreadsheet is automatically downloaded and saved nightly, so any table or name range option would be overwritten by the new file.

Let me show you a bit more of what I'm working with and looking for.

Source file and format- I added a border around the store numbers I'm looking for, but overall this report info cannot change.

This is the list of stores used in the example

This is what I'm hoping the final report will look like.

I can use an INDEX MATCH formula for column C, but it's the one for column A I'm having a problem with. I figured it would be similar to the INDEX IF ROW formula that you wrote, but since the reference for the store list is variable that's where I'm running into a problem.

Answer 4

Refer below image:

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell G2 & copy down:

=IFERROR(INDEX($A$2:$A$100,MATCH(1,COUNTIF(G$1:G1,$A$2:$A$100)+IF(COUNTIF($E$2:$E$7,$B$2:$B$100)>=1,1,0),0)),"")

Enter below formula in cell H2 & copy down:

=IF($G2="","",VLOOKUP($G2,$A$2:$B$100,2,FALSE))

Enter below formula in cell I2 & copy down:

=IF($G2="","",VLOOKUP($G2,$A$2:$C$100,3,FALSE))

Regards,

Amit Tandon

If this response answers your question then please mark as Answer.

Answer 5

Is it possible to make the reference in this part of the formula $B$2:$B$15=D$2 to reference a list of stores instead of one? So instead of reference just cell D2, it's referencing a list of 50+ stores?