Large Index Match Lookup

[Guest]

I need to return a value from another column based on another value I'm using
to filter the results. I can use index and match to do this.

=INDEX(D10100,MATCH("New",L10:L100,0))

This returns the value from column D that corresponds with the first
available instance of the word "New" in column L. I would also like to
return the 2nd value and 3rd value associated with the instance of the same
word. I've tried to insert LARGE in a couple of places but I can't seem to
get it to work (no giggling please this is serious business). Any help would
be appreciated.

 

[Biff]

Hi!

Try this formula entered as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(COUNTIF(L$10:L$100,"new")>=ROWS($1:1),INDEX(D$10$100,SMALL(IF(L$10:L$100="new",ROW($1:$91)),ROW(1:1))),"")

Copy down until you get blanks.

Note: ROW($1:$91) refers to the SIZE of your range.

Biff

 

[Aladin Akyurek]

In M9 enter: 0

which is mandatory.

M10, copied down:

=IF((L10<>"")*(L10="New"),LOOKUP(9.99999999999999E+307,$M$9:M9)+1,"")

N9:

=LOOKUP(9.99999999999999E+307,M9:M100)

N10, copied down:

=IF(ROW()-ROW($N$10)+1<=$N$9,LOOKUP(ROW()-ROW($N$10)+1,$M$10:$M$100,$D$10:$D$100),"")


If so desired, ROW()-ROW($N$10)+1 can be replace with ROWS($N$10:N10).

 

[RagDyer]

I like your error trap!
And it is a little shorter then trapping on the Small().<g>