search or vlookup help

[Little Penny]

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.

 

[Don Guillett]

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

 

[Little Penny]

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?

 

[Bob Phillips]

=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]

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]

Sorry, thought you wanted the last.

I can get them all in separate cells

Select say D1:d20, and in the formula bar, enter

=IF(ISERROR(SMALL(IF($A$1:$A$20="DOG",ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX($C$1:$C$20,SMALL(IF($A$1:$A$20="DOG",ROW($A1:$A20),""),ROW($A1:$A20)))
)

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

You can concatenate then with

=D1&" " &D2&" " &D3&" " &D4&" " &D5

--
HTH

Bob Phillips

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

 

[Little Penny]

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


E7=IF(ISERROR(SMALL(IF($A$1:$A$20="DOG",ROW($A1:$A20),""),ROW($A1:$A20))),"",INDEX($C$1:$C$20,SMALL(IF($A$1:$A$20="DOG",ROW($A1:$A20),""),ROW($A1:$A20))))


Hey Bob if I copy and Paste your formula in cell E1. I only get red.
Should I paste the formula in three different cell to get red white
and blue. I little confused.



Thanks for your held.

 

[Bob Phillips]

Little Penny,

Don't put the formula in E1, select E1:E20, add the formula to the formula
bar, and then Ctrl-Shift-Enter it.

It is a block array formula, it applies to an array of cells, so must be
enter as one block.

--
HTH

Bob Phillips

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