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:

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.

[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:

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.

[day92]

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