Expanded Vlookup Issue

Ok here is the deal. I have a form I am trying to work on.

Pretty Much, It is organized by month. I have a drop down I want to have all the months. Next to them I have cells titled Name, Anniversary, Birthday.

So what I want, is when I pull January from the drop down, it pulls ALL the Names of people who have Birthdays or Anniversarys into the cells.

With VLOOKUP I cannot get it to give me more than one outcome. So if i pick January, it simply Displays the first listing.

Here is an example attached.

There's a UDF called VLOOKUPS that does what you want. I installed it into your sheet and activated it in the range.

http://home.pacbell.net/beban/

The code for the functions are called:

VLookups
ArrayCountIf
ArrayDimensions
MakeArray

They are copied into a standard module. Then you enter a standard VLOOKUP type formula:

=VLOOKUPS($H$4,$B$4:$E$17,2)

When you press Enter, a message will appear in the cell telling you how many
cells downward you need to select.

Highlight the cell and drag down enough cells below to complete the requested number (or more if you want expansion room), press F2, then CTRL-SHIFT-ENTER to activate the array.

The below is meant as an alternative to using UDF and also because I wasn't entirely sure I followed the request, ie I wasn't entirely sure if for Jan you wanted to return only those records where Anniversary occurred in Jan (per value in B) or when either of Month of Anniversary / Month of Birth equated to Jan ?

I've attached an example file doing both... helper cells are in Green.

I would add however that for the more complex scenario (either / or) should you have multiple records with same birthday date or same anniversary date you would still run into difficulty regards the listing of duplicates.

EDIT: you could of course use a Pivot Table with Month as a Page Field / Report Filter (pre 07 / 07 terminology)

Originally Posted by melegaunt

Hmm seems my example isnt working right.
The array thing works wonderfully. But I believe i set the sheet up wrong.

The end result I wanted, was to pick January, and all the january Birthdays appear, and all the january anniversaries appear. Either one would qualify to show up.

But since I am using the Phrase "January" I think it doenst know how to look it up correctly. As it is omitting people who have Jan anniversaries.

Did you review the file I posted -- in particular I would draw your attention to the setup in N:P which was to show how you could pull the data based on the above scenario.

Another alternative... with no helper columns....

This formula in F1 counts total records that have month from drop down in either the Birthday or the Anniversary or Both.

=SUMPRODUCT((TEXT($C$2:$C$10,"mmmm")=$G$2)+(TEXT($D$2:$D$10,"mmmm")=$G$2))-SUMPRODUCT((TEXT($C$2:$C$10,"mmmm")=$G$2)*(TEXT($D$2:$D$10,"mmmm")=$G$2))

This formula in H2, begins to extract the matching information and pulls only the number of records indicated by the formula in F1:

=IF(ROWS($A$1:$A1)>$F$1,"",INDEX(A$2:A$10,SMALL(IF((TEXT($C$2:$C$10,"mmmm")=$G$2)+(TEXT($D$2:$D$10,"mmmm")=$G$2),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($A$1:$A1))))

the above formula is an array formula and must be confirmed with CTRL+SHIFT+ENTER keys instead of just ENTER.. you will see { } brackets appear around the formula...then copy it down as far as you want and across the other relevant number of columns.

If you adjust the ranges to suit a large database, you will need to reconfirm the formula with the CSE keys and then copy down and across again.

See attached.

Here's a thread using a similar formula and I try to explain how it works...

http://www.excelforum.com/excel-work...ml#post1833718

The Countif() part is replaced by the Sumproduct() function reference to F1 in your sheet... it makes for a little better efficiency to have the Counting formula in a separate cell so that it is calculated once and not every time the array formula is copied to another cell.

The + sign between arguments is an array formula's way of saying "OR"... so if one argument or the other is true, then count.

see if it helps..

If you need further clarification please ask.

Actually, we can use a much simplified and more efficient non-array formula to gather all the remaining data after you extract the names in column H.

So replace the formulas in columns I, J, K with these, respectively:

=IF($H2="","",VLOOKUP($H2,$A$2:$D$10,2,0))

=IF($H2="","",IF(TEXT(VLOOKUP($H2,$A$2:$D$10,3,0),"mmmm")=$G$2,VLOOKUP($H2,$A$2:$D$10,3,0),""))

=IF($H2="","",IF(TEXT(VLOOKUP($H2,$A$2:$D$10,4,0),"mmmm")=$G$2,VLOOKUP($H2,$A$2:$D$10,4,0),""))

they only need normal ENTER to confirm...

Then copy them down.

You should get the same info, except only show January dates in relevant columns...