Vlookup

[Abdulkhadar]

Hello Excel Experts,
I want to lookup the values in decreasing order from column A then lookup the data from Column B with matching with decreasing ordered value at Column F. i. e as follows

[TABLE="width: 528"]
<colgroup><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]marks[/TD]
[TD]name[/TD]
[TD][/TD]
[TD]rank[/TD]
[TD][/TD]
[TD] name
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]580[/TD]
[TD]amk[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] =LARGE(A:A,D2)[/TD]
[TD] amk
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]475[/TD]
[TD]rak[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] =LARGE(A:A,D3)[/TD]
[TD] dam
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]525[/TD]
[TD]and[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD] =LARGE(A:A,D4)[/TD]
[TD] znd
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]568[/TD]
[TD]znd[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] =LARGE(A:A,D5)[/TD]
[TD] tab
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]567[/TD]
[TD]arm[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] =LARGE(A:A,D6)[/TD]
[TD] arm
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]580[/TD]
[TD]dam[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]375[/TD]
[TD]tan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]568[/TD]
[TD]tab[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I use the formula at col F =VLOOKUP(LARGE(A:A,D2),A:B,2,FALSE)
but it shows the same name for the same value.
Thanks in advance

 

[Aladin Akyurek]

Book1
	A	B	E	F
1	marks	name	marks	name
2	580	amk	580	amk
3	475	rak	580	dam
4	525	and	568	znd
5	568	znd	568	tab
6	567	arm	567	arm
7	580	dam	525	and
8	375	tan	475	rak
9	568	tab	375	tan
10
Sheet1

In E2 just enter and copy down:

=LARGE($A$2:$A$9,ROWS($E$2:E2))

In F2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E2,ROW($B$2:$B$9)-ROW($B$2)+1),COUNTIFS($E$2:E2,E2))))

 

[Abdulkhadar]

	A	B	E	F
marks	name	marks	name
amk	amk
rak	dam
and	znd
znd	tab
arm	arm
dam	and
tan	rak
tab	tan

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

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

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

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

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

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

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

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

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

[TD="align: center"]6[/TD]
[TD="align: right"]567[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]580[/TD]

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

[TD="align: center"]8[/TD]
[TD="align: right"]375[/TD]

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

[TD="align: center"]9[/TD]
[TD="align: right"]568[/TD]

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

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

</tbody>

Sheet1​

In E2 just enter and copy down:

=LARGE($A$2:$A$9,ROWS($E$2:E2))

In F2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E2,ROW($B$2:$B$9)-ROW($B$2)+1),COUNTIFS($E$2:E2,E2))))

Wow nice, thanks sir,

 

[Aladin Akyurek]

Wow nice, thanks sir,

You are welcome.