Multiple VLookup with dupes

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Dec 22, 2004

• #2

Hi,

=IF(ROW()-ROW($D$1)>COUNTIF($A$2:$A$10,$D$1),"",INDEX($B$1:$B$10,SMALL(IF($A$2:$A$10=$D$1,ROW($A$2:$A$10)),ROW()-ROW($D$1))))

is entered with Ctrl + shift + enter in D2, then dragged down.

Book1.xls
	A	B	C	D
1	Date	No		2002-10-12
2	2002-10-10	100		102
3	2002-10-11	101		103
4	2002-10-12	102		104
5	2002-10-12	103		106
6	2002-10-12	104		107
7	2002-10-10	105		108
8	2002-10-12	106
9	2002-10-12	107
10	2002-10-12	108
Sheet2

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 22, 2004

• #3

Hi,

Book3
	A	B	C	D
1	Date	No		10/12/2002
2	10/10/2002	100		102
3	10/11/2002	101		103
4	10/12/2002	102		104
5	10/12/2002	103		106
6	10/12/2002	104		107
7	10/10/2002	105		108
8	10/12/2002	106		NotFound
9	10/12/2002	107
10	10/12/2002	108
Sheet1

Using Justin Labenne's UDF.

This UDF can be viewed in this post.

http://www.mrexcel.com/board2/viewtopic.php?p=582498#582498

Kris

 

Upvote 0

M

mrh15

New Member

Joined

Oct 20, 2003

Messages

30

• Dec 22, 2004

• #4

When copying the above all I get in column d is "#name?" when I am sure I am copied it correctly?

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Dec 22, 2004

• #5

If you are talking about the formula in krishnakumar's post, it is propably because you have not installed the UDF he is referring to.

If you are talking about my formula, I think you could only get #Name! error if you are running a different language version.

 

Upvote 0

M

mrh15

New Member

Joined

Oct 20, 2003

Messages

30

• Dec 22, 2004

• #6

Ok after pressin alt f11 and pasting the UDF in all I now get is #vallue!

Cheers

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Dec 22, 2004

• #7

I have not tried the UDF and I never promote the use of such when a native formula solution is at hand. So; did you try the formula I suggested?

 

Upvote 0

M

mrh15

New Member

Joined

Oct 20, 2003

Messages

30

• Dec 22, 2004

• #8

Pasted wrong code

CHEERS

Looking GOOD!!!!

 

Upvote 0

M

mrh15

New Member

Joined

Oct 20, 2003

Messages

30

• Dec 22, 2004

• #9

UDF works ok but is very sloooowww

May try your formular mate

 

Upvote 0