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

 

[AlanY]

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

ok, I see.

=INDEX($B$1:$B$11 .. is to get the row reference when a match is found.
e.g. if the second 50 is found in A10 as in post#9, =INDEX($B$1:$B$11 will look at Col B and row 10 for the matched data.

hope this help

 

[AlanY]

of course, you can change the index ref, to start at B3 in this example

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$3:$B$11,SMALL(IF($A$6:$A$11=D6,ROW($A$6:$A$11)-2),COUNTIF(D$6:D6,D6)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LloydFinancials]

Excel 2010
	A	B	C	D	E
1	10	19		10	19
2	80	11		20	2
3	20	2		20	4
4	60	3		20	8
5	20	8		50	5
6	50	5		60	3
7	100	60		80	11
8	20	4		100	10
9	100	10		100	60
Sheet3

Cell Formulas
Range	Formula
E1	{=SMALL(IF(A$1:A$9=D1,B$1:B$9),COUNTIF(D$1:D1,D1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[John Col]

Dear AlanY

Many thanks again. Much appreciated.
I get it, you use a correction with the -2 to index the correct cells.

Thumbs up.
Best wishes,
John

 

[John Col]

Excel 2010

	A	B	C	D	E
1	10	19		10	19
2	80	11		20	2
3	20	2		20	4
4	60	3		20	8
5	20	8		50	5
6	50	5		60	3
7	100	60		80	11
8	20	4		100	10
9	100	10		100	60

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula E1 {=SMALL(IF(A$1:A$9=D1,B$1:B$9),COUNTIF(D$1:D1,D1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Dear Mr LlozdFinancials,

Thanks a lot. Seems great. Also nice that you put column E in the right order. I already did this with a far too difficult formula.... . This is much easier. What I mean is when for example there is 3 times a 20, (like in your example) it will put the numbers 2,4 and 8 in the correct order. I do not know whether you did this on purpose but I like it.
Many thanks.

Best wishes,
John Col

 

[John Col]

Excel 2010

A	B	C	D	E
1	0	19		0	0
2	80	11		20	2
3	20	2		20	4
4	60	3		20	8
5	20	8		50	5
6	50	5		60	3
7	100	60		80	11
8	20	4		100	10

<tbody>
</tbody>

Dear Mr Lloydfinancials,

Actually there is one problem with your formula.
When for example cell A1 = 0 and you delete row 9 it will turn into two times a 0 which is not true.
What i mean is that the values are not flexible when there are no values in row for example row 9.


Best wishes,
John

 

[LloydFinancials]

Thank you for pointing that out John. This should take care of it:
=SMALL(IF(INDIRECT("A$1:A$" &COUNTA(A:A))=D1,INDIRECT("B$1:B$" & COUNTA(A:A))),COUNTIF(D$1:D1,D1))
Luke

 

[John Col]

Thank you for pointing that out John. This should take care of it:
=SMALL(IF(INDIRECT("A$1:A$" &COUNTA(A:A))=D1,INDIRECT("B$1:B$" & COUNTA(A:A))),COUNTIF(D$1:D1,D1))
Luke

Dear Luke,

Really nice, thumps up.
How many years you needed to gain so much experience?
In contrast, I was thinking I know a lot about exel but spending 2 days on this forum decreased my believe of this a lot .

Best wishes,
John

 

[LloydFinancials]

5 months of 5 to 6 hours a day and I still only know a tiny fraction