vlookup..index/match...array???
Posted by zen on June 07, 2001 6:40 AM
I have a problem? it's this, i have a master data sheet with 1000+ names (rows down) with info (columns across). now i can use both vlookup and index/match pretty well. i need a formula that returns the name ("secondname"&" "&"firstname") in a1 if there "startdate" is within this week (b4 everyone piles answers on me, i've done this). Now the problem is in a2 want the next instant of this to be diplayed, and a3, a4 and so on, is there some kind of array or formula that will look at a1 and dicount from the search. i'd prefer a 1 of formula that can go into a2,a3,a4etc. so i don't have to keep writing something different.
sorry if this isn't very well explained but it confusing me.
thanx for n e hlp.
zen
Posted by cpod on June 07, 2001 10:52 AM
If first name is in column D, last name in column E, date in column F and the date you are matching is in H1 then place this formula in A1 and copy down:
=INDEX((D1:D8)&" "&(E1:E8),SMALL(IF($F$1:$F$8=$H$1,ROW($F$1:$F$8),""),ROW()),1)
This is an array formula and must be entered using Control+Shift+Enter.
Posted by zen on June 07, 2001 3:43 PM
that's most delightfull, thanx for that, it works just dandy, at the minute it returning a num!, i don't understand why the IF statement isn't working (i've not had a long look at yet) but for the instance i'm using it for i can CONDITIONAL FORMAT =iserr it out, but a problem mat occur if i have to counta these (which is likely) in the future, any ideas??
thanx again (very much)
zen
Posted by cpod on June 08, 2001 5:49 AM
Could you just use a countif() on the date column?
