vlookup with multiple occurances of same value?

M

muizac

Board Regular
Joined
Oct 24, 2006
Messages
50
Hi there,

I'm struggling to solve this problem. I'd like to list all people with a particular value - when i try a traditional vlookup it only returns the first instance.

My source data is

A........B
55.....John
54.....Peter
55.....Susan
13.....Fred
55.....Paul
53.....Clare
54.....Kevin

In Column C down - I'd like to list of all names with a 55 value. In column D down, a list of everyone with a 54 value etc. Column E = 53 etc

Many thanks
Andrew
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
muizac,


Is this what your raw data looks like, with the results?


Excel Workbook
ABCDEF
1NumberName55545313
255JohnJohnPeterClareFred
354PeterSusanKevin
455SusanPaul
513Fred
655Paul
753Clare
854Kevin
9
Sheet1
 
Upvote 0
That's exactly it mate

This was sample data - the full data list actually has 25 possible values - 55,54,53,52,51,45,44,43,42,41,35,34,33,32,31,25,24,23,22,21,15,14,13,12,11 - so there will be 25 extra columns (column C - Z) to list the names in.

But what you have here is spot on. Is the formula to produce this difficult?

(Hope the snow digging went ok)

Cheers,
Andrew

(Hope

hiker95 said:
muizac,


Is this what your raw data looks like, with the results?


Excel Workbook
ABCDEF
1NumberName55545313
255JohnJohnPeterClareFred
354PeterSusanKevin**
455SusanPaul***
513Fred****
655Paul****
753Clare****
854Kevin****
9******
Sheet1
Click to expand...
 
Upvote 0
muizac,


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1NumberName55545352514544434241353433323125242322211514131211
255JohnJohnPeterClareFred
354PeterSusanKevin
455SusanPaul
513Fred
655Paul
753Clare
854Kevin
Sheet1





The formula in cell C2 (confirmed with CTRL + SHIFT + ENTER) not just ENTER, then copied down to the end of your data in column B, then (in this case) copy the range C2:C8 to D2:AA2:

=IF(ISERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=C$1,ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($C$2:C2)))),"",INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=C$1,ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($C$2:C2))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Sports competition top scorer list (round by round)
2
Replies
17
Views
261
Kaper
Kaper
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
524
XLfriend777
XLfriend777
M
  • Solved
can I do a lookup to search a range of cells for any one of a list of values?
Replies
8
Views
716
Peter_SSs
Peter_SSs
N
  • Solved
Formula which returns any all values that occur twice in a two-dimensional range
Replies
6
Views
504
Fluff
Fluff
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
275
MeaclH
M

Forum statistics

Threads
1,224,507
Messages
6,179,176
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top