Lookup/Match Formula

[day92]

I have a spreadsheet I'm working on where I need to look up plan codes. I have about 500 plan codes I need to look up. What I'm trying to do is return the DOB when there is a match for a plan code. Problem is at times there are multiple plan codes and I need to return all the DOB’s when a plan code is found.

There is an example of this in the attached sheet. Does anyone have any idea on how to set this up?

[NBVC]

If you are looking to return the matching DOB's in the same cell, then you can use a UDF:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and then apply formula in A2:

=SUBSTITUTE(TRIM(aconcat(IF(data!$A$2:$A$6=B2,data!$D$2:$D$6,"")," "))," ",",")

adjusting ranges to suit and then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

[day92]

Actually I would like to have them in separate rows. Once I have them populated I will do a Vlookup to get the rest of the information needed.

See the attached spreadsheet. Will the UDF work for this?

[NBVC]

No that UDF won't do that.

You need a program that will automatically insert the correct number of rows as is scans through each row one by one.... you'll need someone with better VBA skills than I presently have though....

[day92]

Actually I can use this, it will just take a little bit of time but definitely save me a day's worth of work.

Thanks for the assistance.

[NBVC]

This could be a formula alternative:

Try this, in C2 enter:

=1+COUNTIF(data!A:A,B2)+C1-(COUNTIF(data!A:A,B2)>0)

copied down to bottom

In D2 enter:

=MAX(C:C)

in E2 enter:

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX($B$2:$B$84,MIN(IF(ROWS(A$2:A2)<=$C$2:$C$84,ROW($C$2:$C$84)-ROW($C$2)+1))))

confirmed with CTRL+SHIFT+ENTER and copied down beyond the bottom until you get blanks.

in F2 enter:

=IFERROR(INDEX(data!$D$2:$D$6,SMALL(IF(data!$A$2:$A$6=E2,ROW(data!$A$2:$A$6)-ROW(data!$A$2)+1),COUNTIF(E$2:E2,E2))),"")

adjust ranges for data! sheet and confirmed with CTRL+SHIFT+ENTER and copied down

You can then copy and Paste special >> Values over the original 2 columns if desired and remove these formula columns.

[day92]

Genius! Works like a charm. You are definitely an asset to the Excel community