Attempting to pull back all matching values in a 2 column table using INDEX. One row off?

[Bitcain]

Image-

This worked perfectly at first, so I thought. But for some reason it is pulling the letter 1 down from it's match (See highlighted matches and it returns that below it). The far right in red is a text only sample of desired outcome from the data on the far left.

I've tried so much. I found that formula [FONT=inherit !important][FONT=inherit !important]online[/FONT][/FONT] and have been messing with it for over 2 hours.

My original need was to auto find the possibilities (1,2,3, and 4) Then have them formulate themselves to the right without so much manual formula input; however, I can't figure out how. VLOOKUP only seems to return one result, this seems to be my best best and having to manually enter the lookup value(A2, A7, Etc) and paste the formula as well as modifying all of the cell references.

I'm open to anything. I'm not good enough for Macros/[FONT=inherit !important][FONT=inherit !important]VBA[/FONT][/FONT] yet obviously.





The best scenario is it would find each unique identifier "pump" and add it to the column then sort out the options "product" at that pump.
Ex: It searches the column and finds 1-4, It then puts 1 and all of it's options, then 2, etc.. Not sure if that's reasonable.

{=IF(ROWS($F$2:F2)>E$2,"",INDEX(Product,SMALL(IF(Pump=D$2,ROW(Pump)),ROWS($F$2:F2))))}

 

[sheetspread]

A non macro approach would be to sort by pump number, then use a conditional format to hide (white out) the repeating numbers.

 

[Bitcain]

Problem is that column A and B will be frequently changing so I'm trying to have the data update as well.


I'm really trying to make this so others can use it with ease. Sounds like I need to learn macros?

 

[sheetspread]

A dynamic named range will allow for that, but if you're intent on a formula solution I'll try to find the unique list/lookup without helper columns.

 

[Bitcain]

I'll try to find the unique list/lookup without helper columns.

Something like How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource ??

 

[sheetspread]

This one will be different because you're hiding the duplicates, not removing them

 

[sheetspread]

Like this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Pump
2	A	1
3	B	2								Pump	Products
4	V	3								1	A
5	B	1									B
6	A	1									A
7	B	2									B
8	D	2									F
9	B	1								2	B
10	B	2									B
11	S	3									D
12	B	4									B
13	F	1								3	V
14	R	4									S
15	S	3									S
16	S	4								4	B
17	B	4									R
18											S
19											B
Sheet2 (4)

Cell Formulas
Range	Formula
J4	=IF(ROW(A1)=1,SMALL($B$2:$B$17,ROW(A1)),IF(SMALL($B$2:$B$17,ROW(A1))=SMALL($B$2:$B$17,ROW(A1)-1),"",SMALL($B$2:$B$17,ROW(A1))))
K4	{=INDEX($A$2:$A$17,SMALL(IF($B$2:$B$17=SMALL($B$2:$B$17,ROW(A1)),ROW(A2:A17)-ROW(A2)+1),ROW(A1)-MATCH(SMALL($B$2:$B$17,ROW(A1)),$J$4:$J$19,0)+1))}
Press CTRL+SHIFT+ENTER to enter array formulas.