how to return mulitple corresponding values
The generic method is like this:
=IF(ISERROR(SMALL(IF(A$1:A$10=lookup_value,ROW($1: $10)),ROW(1:1))),"",INDEX(B$1:B$10,SMALL(IF(A$1:A$ 10=lookup_value,ROW($1:$10)),ROW(1:1))))
However, that's not very efficient or robust. If you provide some details we
can come up with something that's better.
--
Biff
Microsoft Excel MVP
"Chris" wrote in message
...
When I drag it down and fill in the cells, i get #NUM as it cannot locate
any
more matches. How do i ISERROR that out to return a "-" after it meets the
end threshold.
"T. Valko" wrote:
How about providing some specific details about what you're wanting to
do.
--
Biff
Microsoft Excel MVP
"Chris" wrote in message
...
Awesome, I have been searching for this... How do i mod it for to
return
a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.
"Biff" wrote:
Hi!
The basic formula is something like this:
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))
Then copy down.
Where column A contains the lookup_value and column B contains the
values
to
be returned.
Need more specific details to offer a more robust suggestion.
Biff
"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name
occurs
on.
Vlookup returns only one value. How can I get multiple values?
.
|