How to make Vlookup return next match

[romeo16]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]
I have a huge table but I just made this as a sample of my scenario , so I used LARGE() to return first three Old ages and this was the result which makes sense.

[TABLE="class: grid, width: 80"]
<tbody>[TR]
[TD]30[/TD]
[/TR]
[TR]
[TD]25[/TD]
[/TR]
[TR]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

And then I want to return the corresponding value (Name) from the first table . I used Vlookup but If you know another function that can work better . Vlookup result was like this

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

And I want it to be as below

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

Any help will much appreciated. Thanks

 

[Eric W]

Welcome to the forum.

It can be done, but it's a little tricky.

	A	B	C	D	E	F
Names	Age	Top 3
A	D
B	A
C	C
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=E2,ROW($B$2:$B$5)-ROW($B$2)+1),COUNTIF($E$2:$E2,E2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



E2:E4 is where you'd put your LARGE functions. Then the formula in F2 is an array formula, confirm it with Control+Shift+Enter. Then you can drag it down the column.

It essentially works by compiling a list of the rows that match the value in column E, then does a COUNTIF to find out how many have been used so far, then the SMALL to get the appropriate row, then the INDEX to get the name.

Let me know if you have any questions.

 

[wideboydixon]

Beaten to it but I'm going to post anyway since I just worked on this

Book1
	A	B	C	D	E
1	Name	Age		Oldest	Name
2	A	30		30	A
3	B	21		30	L
4	C	29		29	C
5	D	20
6	E	27
7	F	24
8	G	18
9	H	20
10	I	28
11	J	18
12	K	20
13	L	30
14	M	19
15	N	24
16	O	20
17	P	23
18	Q	28
19	R	29
20	S	25
21	T	22
22	U	26
23	V	25
24	W	25
25	X	22
26	Y	18
27	Z	20
Sheet1

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

WBD

 

[romeo16]

I know I came to the right place. Thanks Eric it's working now

 

[Eric W]

Glad we could help!

 

[romeo16]

Glad we could help!

but I don't understand that array formula, could you please explain how it works? Thanks

 

[wideboydixon]

{=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=E2,ROW($B$2:$B$5)-ROW($B$2)+1),COUNTIF($E$2:$E2,E2)))}

The piece that needs the array formula is this:

IF($B$2:$B$5=E2,ROW($B$2:$B$5)-ROW($B$2)+1)

It iterates over the ages and returns an array of rows that contain a match: {FALSE, FALSE, FALSE, 4} for the "30" and then {1, FALSE, 3, FALSE} for the "25".
We then use SMALL() to find "nth" smallest match. For n, we use:

COUNTIF($E$2:$E2,E2) - this will become COUNTIF($E$2:$E3,E3) in the next row down and COUNTIF($E$2:$E3,E4) in row 4. These will return 1, 1, 2 respectively so we have:

SMALL({FALSE, FALSE, FALSE, 4},1) = 4
SMALL({1, FALSE, 3, FALSE}, 1) = 1
SMALL({1, FALSE, 3, FALSE}, 2) = 3

Those values are then used by INDEX to retrieve the name from the calculated row.

Hope that makes sense. Eric can thank me later

WBD

 

[Eric W]

Thank you, WBD! I knew you'd be able to help, with that brain the size of a planet!

Romeo, let us know if you have any other questions.

 

[romeo16]

Thanks for that however I still don't get it , you said small returns the smallest match , right? why here SMALL({1, FALSE, 3, FALSE}, 2) = 3
, It didn't return 1?

 

[wideboydixon]

SMALL({array}, n) returns the "nth" smallest value from the array and won't include FALSE. In this case, n = 2 so it returns the second smallest number = 3.

WBD