vlookup with multiple occurances of same value?

muizac · Jan 12, 2011

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

hiker95 · Jan 12, 2011

muizac,

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

Excel Workbook

A

B

C

D

E

F

1

Number

Name

55

54

53

13

2

55

John

Peter

Clare

Fred

3

54

Peter

Susan

Kevin

4

55

Susan

Paul

5

13

Fred

6

55

Paul

7

53

Clare

8

54

Kevin

9

Sheet1

hiker95 · Jan 12, 2011

muizac,

Have to dig out from the snow storm.

Be back later.

muizac · Jan 12, 2011

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
A B C D E F
1 Number Name 55 54 53 13
2 55 John John Peter Clare Fred
3 54 Peter Susan Kevin * *
4 55 Susan Paul * * *
5 13 Fred * * * *
6 55 Paul * * * *
7 53 Clare * * * *
8 54 Kevin * * * *
9 * * * * * *
Sheet1

hiker95 · Jan 12, 2011

muizac,

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Number

Name

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

2

55

John

Peter

Clare

Fred

3

54

Peter

Susan

Kevin

4

55

Susan

Paul

5

13

Fred

6

55

Paul

7

53

Clare

8

54

Kevin

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))))

vlookup with multiple occurances of same value?

muizac

Board Regular

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

muizac

Board Regular

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

vlookup with multiple occurances of same value?

muizac

Board Regular

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

muizac

Board Regular

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock