How to use vlookup to display 2 same values.

[leno]

Hi guys,

I was trying to use vlookup to search column A and B and display the lowest 2 total numbers for each seat. But somehow, I got 2 seats with the number 4 displayed instead of 4 and 6. Seems like vlookup will only search and display the first match found. Really could appreciate it if someone could enlighten me on what I need.

A B
Total Seat no.
19 1
19 2
20 3
17 4
18 5
17 6

first lowest : 4
second lowest: 4

 

[SkipVought]

Hi,

1. sort by Total

2. use Insert > Name > Create - Create names in TOP row to create named ranges for Total and Seat_no.

3. in column D, starting in row 2, enter 17, 18, 19, 20 for lookup values from total.

4. in row 1, starting in column E enter 1, 2.

5. the formula, assuming that your data is in columns A & B, starting in row 1...
[tt]
E2: =INDEX(OFFSET($A$1,MATCH($D2,Total,0),1,COUNTIF(Total,$D2),1),E$1,1)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...on yes, the results...
[tt]
1 2
17 4 6
18 5 #REF!
19 1 2
20 3 #REF!
[/tt]
the #REF! indicates that there are not two values in the range for that lookup value.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}