Vlookup with Duplicates

[John Col]

Dear All,

I am struggling with a formula for a week now and really would like to find a solution to this problem. I have read a lot of forums/websites and did not found the solution yet

It is relative easy to explain:

Raw data is
-----------Column 1-----------Column 2

Row 1--------------20------------------2
Row 2--------------80------------------4
Row 3--------------60------------------3
Row 4--------------50------------------5
Row 5--------------50------------------8
Row 6-------------100-----------------10

Below, I sort column 1 from low to high with the SMALL function. The question is how to have the same values in column 2 as in the raw data.
I can not use a standard vlookup function because it will display two times a "5" in column 2 because of raw 2 and 3 has a value 50 .

New data is
------------Column 1--------Column 2--------I need to have in column 2 the values > as in the raw data

Row 1--------------20---------------- ?--------------2 This is the outcome I am looking for
Row 2--------------50-------------- --?--------------5 This is the outcome I am looking for
Row 3--------------50-----------------?--------------8 This is the outcome I am looking for
Row 4--------------60-----------------?--------------3 This is the outcome I am looking for
Row 5--------------80-----------------?--------------4 This is the outcome I am looking for
Row 6-------------100-----------------?-------------10 This is the outcome I am looking for

Thank you very very very much.

Hope someone have the solution to this problem .

Best wishes,
John Cole

 

[John Col]

Ps. the numbers are not fixed.

 

[AlanY]

i might have missed something, if you sort your data with Data > Sort > Column A > smallest to largest, you should have the desired results

 

[John Col]

Dear AlanY,

First of all thank you for your time to have look at my problem. The problem is that it have to be a function build in to the cells. Without using data>sort> column A. That is also one of the reasons why I used the SMALL function.

Thanks again,
John

 

[AlanY]

raw data in A & B, new data in D & E;

Excel 2012
	A	B	C	D	E
1	Raw Data			New Data
2	20	2		20	2
3	80	4		50	5
4	60	3		50	8
5	50	5		60	3
6	50	8		80	4
7	100	10		100	10
2

Excel 2012
	E
2	2
2

Cell Formulas
Range	Formula
E2	{=INDEX($B$1:$B$7,SMALL(IF($A$2:$A$7=D2,ROW($A$2:$A$7)),COUNTIF(D$2:D2,D2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[John Col]

AlanY,

Champ! That is great, thank you so much!!!
If you were at my office I would bring the coffee for the entire week.
I was struggling with it so long pfff.
One question to understand the formula, It only works when index starts at B1, as you did > which is empty, why is it not working from B2?

Thank again,
Best wishes,
John Col

 

[AlanY]

you're welcome.

btw, black, double shots, no sugar.

 

[AlanY]

AlanY,

Champ! That is great, thank you so much!!!
If you were at my office I would bring the coffee for the entire week.
I was struggling with it so long pfff.
One question to understand the formula, It only works when index starts at B1, as you did > which is empty, why is it not working from B2?

Thank again,
Best wishes,
John Col

not quite understand your question.
can you explain a bit more?

 

[AlanY]

this still works, just need to adjust the formula accordingly

Excel 2012
	A	B	C	D	E
1
2
3
4
5	Raw Data			New Data
6	20	2		20	2
7	80	4		50	5
8	60	3		50	8
9	50	5		60	3
10	50	8		80	4
11	100	10		100	10
2

Excel 2012
	E
6	2
2

Cell Formulas
Range	Formula
E6	{=INDEX($B$1:$B$11,SMALL(IF($A$6:$A$11=D6,ROW($A$6:$A$11)),COUNTIF(D$6:D6,D6)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[John Col]

Dear AlanY ,

It is only a small question to unstand the formula.
{=INDEX($B$1:$B$7,SMALL(IF($A$2:$A$7=D2,ROW($A$2:$A$7)),COUNTIF(D$2:D2,D2)))}
As you can see the INDEX start at =INDEX($B$1 (an empty cell) > why is it not =INDEX($B$2. It only works when it starts at this $B$1 as you did.

Best wishes,
John