Large Index Match Lookup
 

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(D10:D100,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.

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:D$100,SMALL(IF(L$10:L$100="new",ROW($1:$91)),RO W(1:1))),"")

Copy down until you get blanks.

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

Biff

"Qaspec" wrote in message
...
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(D10:D100,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.

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).


Qaspec wrote:
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(D10:D100,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.

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" wrote in message
...
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:D$100,SMALL(IF(L$10:L$1
00="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

"Qaspec" wrote in message
...
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(D10:D100,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.