Expanded Vlookup Issue

**melegaunt** · 01-25-2009, 12:52 AM

Originally Posted by DonkeyOte

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.

Actually I am trying to wrap my head around this still. I honestly have the file you made and the file I am working with side by side. There is alot of stuff implemented into your example. I just recently started getting into the more advanced stuff in Excel. The Match,Index combination of things I am having some trouble following.

Another Issue I have is that they have reformatted the file I am working on, so the information is appearing slightly different.

Now there are columns for:
Team Member Name
Team Name
Date of Birth
Anniversary

Here is the example of the new format attached
Keep in mind this list of names will probably be about 1000 names long.

By the way, thanks for all of your help already.

**NBVC** · 01-25-2009, 01:18 AM

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.

**melegaunt** · 01-25-2009, 01:25 AM

Well thats exactly what I am after!

Now I will have to mull over it for awhile to make sure I understand all of it and to Anniversary/Birthday cells not show a number when they dont apply to the date range.

Thank you again for all of this. It looks like I need alot more practice with arrays.

Expanded Vlookup Issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions