Vlookup to get all the values from given dataset

[harinsh]

Hi Team,

I hope anyone can help me with below requirement. When we do vlookup ...we only get value based on look up or same row values based on the position of the column.

I need something different like need to get the values based look up and next lookup value should not be same value ...and it should be next against lookup value.

Here is the input and output examples for my requirement ( I can't sort the data which I have)

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]
Input :
Emp No[/TD]
[TD="class: xl64, width: 64"]

Values[/TD]
[/TR]
[TR]
[TD="class: xl63"]111111[/TD]
[TD="class: xl64"]50[/TD]
[/TR]
[TR]
[TD="class: xl63"]111111[/TD]
[TD="class: xl64"]53[/TD]
[/TR]
[TR]
[TD="class: xl63"]222222[/TD]
[TD="class: xl64"]34[/TD]
[/TR]
[TR]
[TD="class: xl63"]222222[/TD]
[TD="class: xl64"]32[/TD]
[/TR]
[TR]
[TD="class: xl63"]333333[/TD]
[TD="class: xl64"]66[/TD]
[/TR]
[TR]
[TD="class: xl63"]333333[/TD]
[TD="class: xl64"]54[/TD]
[/TR]
[TR]
[TD="class: xl63"]444444[/TD]
[TD="class: xl64"]88[/TD]
[/TR]
[TR]
[TD="class: xl63"]111111[/TD]
[TD="class: xl64"]86[/TD]
[/TR]
[TR]
[TD="class: xl63"]222222[/TD]
[TD="class: xl64"]34[/TD]
[/TR]
[TR]
[TD="class: xl63"]222222[/TD]
[TD="class: xl64"]23[/TD]
[/TR]
</tbody>[/TABLE]

Ouput:
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 64"]Emp ID[/TD]
[TD="class: xl67, width: 64"]Values[/TD]
[/TR]
[TR]
[TD="class: xl66"]111111
[/TD]
[TD="class: xl65, align: right"]50
[/TD]
[/TR]
[TR]
[TD="class: xl66"]111111
[/TD]
[TD="class: xl65, align: right"]53
[/TD]
[/TR]
[TR]
[TD="class: xl66"]111111
[/TD]
[TD="class: xl65, align: right"]86
[/TD]
[/TR]
[TR]
[TD="class: xl66"]222222
[/TD]
[TD="class: xl65, align: right"]34
[/TD]
[/TR]
[TR]
[TD="class: xl66"]222222
[/TD]
[TD="class: xl65, align: right"]32
[/TD]
[/TR]
[TR]
[TD="class: xl66"]222222
[/TD]
[TD="class: xl65, align: right"]34
[/TD]
[/TR]
[TR]
[TD="class: xl66"]222222
[/TD]
[TD="class: xl65, align: right"]23
[/TD]
[/TR]
[TR]
[TD="class: xl66"]333333[/TD]
[TD="class: xl68"] 66[/TD]
[/TR]
[TR]
[TD="class: xl66"]333333[/TD]
[TD="class: xl68"] 54[/TD]
[/TR]
[TR]
[TD="class: xl66"]444444[/TD]
[TD="class: xl68"] 88[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 64"]

​

[/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$2:$B$11,SMALL(<font color="Red">IF(<font color="Green">$E2=$A$2:$A$11,ROW(<font color="Purple">$B$2:$B$11</font>)-ROW(<font color="Purple">$B$2</font>)+1</font>),COUNTIFS(<font color="Green">$E$2:$E2,$E2</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013/2016
	A	B	C	D	E	F
1	Emp No	Values			Emp ID	Values
2	111111	50			111111	50
3	111111	53			111111	53
4	222222	34			111111	86
5	222222	32			222222	34
6	333333	66			222222	32
7	333333	54			222222	34
8	444444	88			222222	23
9	111111	86			333333	66
10	222222	34			333333	54
11	222222	23			444444	88
Sheet3