Vlookup data wrong if the small value found are same
 

Dear Friends,

I need to find the smallest 8 amounts from hundreds of data and look up the
data of the corresponding amount. So I used the small function with
referrence to the Rank column and found two -100,000.00 and each of them
related to Joey and Keith. However, the vlookup function retrieved both data
as Joey and lost Keith's Number and Name. Same problem occurs if retrieving
the top 10 amounts with large function.

Thanks for your great help in advance.

Fanny

Rank Amount Number Name
1 -156,250.00 8 John
2 -120,547.00 13 Peter
3 -103,100.00 541 Mary
4 -102,227.00 20 Kary
5 -100,000.00 261 Joey
6 -100,000.00 261 Joey
7 -87,192.00 1 Kelvin
8 -77,250.00 92 Josephe

Vlookup data wrong if the small value found are same
 

You might consider using Data Filter AutoFilter to filter the rows you
want.........using Custom Bottom 8 criteria..........

Vaya con Dios,
Chuck, CABGx3



"Fanny" wrote:

Dear Friends,

I need to find the smallest 8 amounts from hundreds of data and look up the
data of the corresponding amount. So I used the small function with
referrence to the Rank column and found two -100,000.00 and each of them
related to Joey and Keith. However, the vlookup function retrieved both data
as Joey and lost Keith's Number and Name. Same problem occurs if retrieving
the top 10 amounts with large function.

Thanks for your great help in advance.

Fanny

Rank Amount Number Name
1 -156,250.00 8 John
2 -120,547.00 13 Peter
3 -103,100.00 541 Mary
4 -102,227.00 20 Kary
5 -100,000.00 261 Joey
6 -100,000.00 261 Joey
7 -87,192.00 1 Kelvin
8 -77,250.00 92 Josephe

Vlookup data wrong if the small value found are same
 

One way via non-array formulas
(using arbitrary tie-breakers)

Assuming source data in cols A to D, from row2 down
(col B = Amount)

Using empty cols to the right

Put in E2: =IF(B2="","",B2+ROW()/10^10)
Copy E2 down until the last row of data (leave E1 empty)

Put in F2:
=INDEX(A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0))
Copy F2 to I2, fill down to I8

Cols F to I return the required results. Lines with tied amounts
will appear in the same relative order as that in the source.

To get it in "descending" order (using LARGE)

Put in J2: =IF(B2="","",B2-ROW()/10^10)
Copy J2 down until the last row of data (leave J1 empty)

Put in K2:
=INDEX(A:A,MATCH(LARGE($J:$J,ROW(A1)),$J:$J,0))
Copy K2 to N2, fill down to N8

Cols K to N return the required results. Lines with tied amounts
will appear in the same relative order as that in the source.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Fanny" wrote in message
...
Dear Friends,

I need to find the smallest 8 amounts from hundreds of data and look up
the
data of the corresponding amount. So I used the small function with
referrence to the Rank column and found two -100,000.00 and each of them
related to Joey and Keith. However, the vlookup function retrieved both
data
as Joey and lost Keith's Number and Name. Same problem occurs if
retrieving
the top 10 amounts with large function.

Thanks for your great help in advance.

Fanny

Rank Amount Number Name
1 -156,250.00 8 John
2 -120,547.00 13 Peter
3 -103,100.00 541 Mary
4 -102,227.00 20 Kary
5 -100,000.00 261 Joey
6 -100,000.00 261 Joey
7 -87,192.00 1 Kelvin
8 -77,250.00 92 Josephe

Vlookup data wrong if the small value found are same
 

In sheet1 I placed your list (with line 6 changed to represent another
person). In sheet2 I placed the following formulas.

A1: Rank
A2: 1
A3: 2
....

B1: Amount
B2: =SMALL(Sheet1!$B$2:$B$9,A2)
B3: =SMALL(Sheet1!$B$2:$B$9,A3)
.... B3 can be copied down to B9.

C1: Number
C2: =INDIRECT("Sheet1!C"&MIN(IF(Sheet1!$B$2:$B$9=B2,RO W(Sheet1!$B$2:$B$9))))
C3:
=INDIRECT("Sheet1!C"&SMALL(IF(Sheet1!$B$2:$B$9=B3, ROW(Sheet1!$B$2:$B$9)),COUNTIF($B$2:B3,B3)))
.... C3 must be entered using Ctrl+Shift+Enter. Copy C3 down to C9

D1: Name
D2: =INDIRECT("Sheet1!D"&MIN(IF(Sheet1!$B$2:$B$9=B2,RO W(Sheet1!$B$2:$B$9))))
D3:
=INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$B$2:$B$9=B3, ROW(Sheet1!$B$2:$B$9)),COUNTIF($B$2:B3,B3)))
.... D3 must be entered using Ctrl+Shift+Enter. Copy D3 down to D9

Sorce Data looks like this (I sorted it according to Number to test the
ranking)...
Rank Amount Number Name
7 -87,192.00 1 Kelvin
1 -156,250.00 8 John
2 -120,547.00 13 Peter
4 -102,227.00 20 Kary
8 -77,250.00 92 Josephe
5 -100,000.00 261 Joey
6 -100,000.00 275 Keith
3 -103,100.00 541 Mary

The Ranked List looks like this...
Rank Amount Number Name
1 -156250 8 John
2 -120547 13 Peter
3 -103100 541 Mary
4 -102227 20 Kary
5 -100000 261 Joey
6 -100000 275 Keith
7 -87192 1 Kelvin
8 -77250 92 Josephe

you should be able to adjust the formulas according to your range. The
VLOOKUP function becomes useless if you have nonunique entries. Hope this
helps.

"Fanny" wrote:

Dear Friends,

I need to find the smallest 8 amounts from hundreds of data and look up the
data of the corresponding amount. So I used the small function with
referrence to the Rank column and found two -100,000.00 and each of them
related to Joey and Keith. However, the vlookup function retrieved both data
as Joey and lost Keith's Number and Name. Same problem occurs if retrieving
the top 10 amounts with large function.

Thanks for your great help in advance.

Fanny

Rank Amount Number Name
1 -156,250.00 8 John
2 -120,547.00 13 Peter
3 -103,100.00 541 Mary
4 -102,227.00 20 Kary
5 -100,000.00 261 Joey
6 -100,000.00 261 Joey
7 -87,192.00 1 Kelvin
8 -77,250.00 92 Josephe

Vlookup data wrong if the small value found are same
 

.. smallest 8

To really cater for the event of ties, or multiple ties happening, think we
would probably need to fill down (viz. fill F2:L2, or K2:N2 down) beyond 8
rows to extract the top 8 (or bottom 8) list, and then read it off from
there.

--
Max, Singapore GMT+8, xl97
Samples archive at: http://savefile.com/projects/236895
xdemechanik
--