Lookup/Match Formula

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?

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

Please Login or Register  to view this content.

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.

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

This could be a formula alternative:

Try this, in C2 enter:

Please Login or Register  to view this content.

copied down to bottom

In D2 enter:

Please Login or Register  to view this content.

in E2 enter:

Please Login or Register  to view this content.

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

in F2 enter:

Please Login or Register  to view this content.

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.