Solved: Help with a Vlookup function.

[NY2DR]

Hello everyone,

I have been searching for a formula that can possibly do this and had no luck at all.
I could do an autofilter, but I think its overkill.
To do it in VBA, well maybe, but I dont want any buttons present.(I want it all automatic)


Birthdays (Month) October
October Anne Anne
November Bob Stan
January Tom John
October Stan
December Elise
January Mike
October John
November Albert
March Monica

Here's what I want it to do.
First- In the "Birthdays (Month)" section, there are two columns, one that has the month, and the other with the persons name.

Second- On the other column, If I were to change just the month, I would like the corresponding names to appear right below it.

What would be a more improved version of "=VLOOKUP($C$1,$A$2:$B$10,2,FALSE)" to give me those results?

Thank you in advance.

:bug:

[shades]

Howdy. I'm a little confused.




Birthdays (Month) October
October Anne Anne
November Bob Stan
January Tom John
October Stan
December Elise
January Mike
October John
November Albert
March Monica

Here's what I want it to do.
First- In the "Birthdays (Month)" section, there are two columns, one that has the month, and the other with the persons name.

Second- On the other column, If I were to change just the month, I would like the corresponding names to appear right below it.

Do you mean to change the value in C1 from October to November? And then you want the November names to appear below that?


What would be a more improved version of "=VLOOKUP($C$1,$A$2:$B$10,2,FALSE)" to give me those results?



Does this work? Is it slow? If it works, you might want to go with it.

[NY2DR]

Do you mean to change the value in C1 from October to November? And then you want the November names to appear below that?


Yes!!:thumb
I enclosed a worksheet to explian it more clearly.
apparently in text format it didn't come out so clear.

[Bob Phillips]

Select cells C2:C10, then enter this formula in the formula bar

IF(ISERROR(SMALL(IF(A2:A$10=$C$1,ROW($A2:$A$10),""),ROW($A2:$A$10)-ROW($A$2)+1)),"",
INDEX($B$1:$B$20,SMALL(IF(A2:A$10=$C$1,ROW($A2:$A$10),""),ROW($A2:$A$10)-ROW($A$2)+1)))

as an array formula, so commit with Ctrl-Shift-Enter

[geekgirlau]

Another option is to use an advanced filter to copy the values matching your criteria to another location. The attached file has a macro that does this each time the value in the combo box changes.

[Bob Phillips]

.

[Shazam]

Here is another way of doing it. Click in cell B2 and you will see the filters.

[NY2DR]

Thank you all!http://vbaexpress.com/forum/images/smilies/friendship.gif

All methods were very good, but i'm going to stick with XLD's method.
It will be convienient for me to have my calendar just paste a month in the field and presto, all my familys name birthdays pop-up!
(Now I just have to "learn" how XLD's formulas work!)