How can I lookup when match has more than one value?
This is very helpful to me also; however, I have a need to return every match
to a specific cell or in a continuous stream in one cell. The matches are
names that match a particular number. Is there a way to print in columns
specified, each match to the number being looked up?
"T. Valko" wrote:
Here's one way:
Assume data in A2:B20. You want to extract data from column B that
corresponds to a lookup_value.
D2 = lookup_value
Array entered** :
=IF(ROWS($1:1)<=COUNTIF(A$2:A$20,D$2),INDEX(B$2:B$ 20,SMALL(IF(A$2:A$20=D$2,ROW(B$2:B$20)-MIN(ROW(B$2:B$20))+1),ROWS($1:1))),"")
Copy down until you get blanks.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"bonot1" wrote in message
...
Data is in random order, and the data to be returned is text.
"T. Valko" wrote:
Is the data sorted so that the lookup_values are grouped together or is
the
data random? Is the data to be returned text or numeric?
--
Biff
Microsoft Excel MVP
"bonot1" wrote in message
...
I am using LOOKUP functions to retrieve info from a list. Some of the
lookup
values have more than one match in the list. Is there a function that
allows
me to retrieve multiple elements for one lookup value, or at least a
function
that tells me there are duplicate matches?
|