A B C

1 CAT CAT GREEN
2 CAT CAT YELLOW
3 DOG CAT RED
4 CAT CAT ORANGE
5 DOG CAT WHITE
6 CAT CAT BLACK
7 DOG CAT BLUE

Thanks Bop for you rely. I modified your formula for the above example

=INDEX(C1:C100,MAX(IF(A1:A100="DOG",ROW(A1:A100))) )

I only get "BLUE" not "RED WHITE AND BLUE". I also made sure to use
Ctrl-Shift-Enter. How do I combine these value in to one cells or over
serveral cell.















Bob Phillips wrote:
=INDEX(C1:C100,MAX(IF(A1:A100="Bob",ROW(A1:A100))) )

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Little Penny" wrote in message
ups.com...
But as I understand it vlookup will only return the first instance of a
lookup value.
For example If I do a vlookup in column A for the word DOG and return
the value 3 columns to the right and DOG is in Colum A in rows 3,5, and
7 and in column C row 3 is the word RED, and column C row 5 is the word
WHITE and column C row 7 is the word BLUE. The way vlookup works it
will only return the word RED, I want it to return RED, WHITE and BLUE.

Is this not correct?








Don Guillett wrote:
you can do this with vlookup by expanding your range and just using the
column desired
OR
you can use match to find the row and put that in an index formula
or
use offset

--
Don Guillett
SalesAid Software

"Little Penny" wrote in message
oups.com...
I'm trying to get excel lookup a value and than search for that value
in a column and then return the value two columns to the right. I know
this sound like vlookup but I want excel to continue looking after it
finds the first instance of my lookup value because there maybe 3 or 4
instance of my lookup value that have different values two columns to
the right. How can I do this and what function should I use if
possible.