A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How can I lookup when match has more than one value?



 
 
Thread Tools Display Modes
  #1  
Old July 24th, 2007, 02:12 AM posted to microsoft.public.excel.worksheet.functions
bonot1
external usenet poster
 
Posts: 4
Default How can I lookup when match has more than one value?

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?
  #2  
Old July 24th, 2007, 03:59 AM posted to microsoft.public.excel.worksheet.functions
David Hilberg
external usenet poster
 
Posts: 60
Default How can I lookup when match has more than one value?

=IF( CountIf(a1:a10,"Joe")1, CountIf(a1:a10,"Joe"),
LookUp(..etc...) )

will give you the count if there are more or fewer than one.
Otherwise, it performs the lookup.

- David

On Jul 23, 9:12 pm, bonot1 wrote:
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?



  #3  
Old July 24th, 2007, 04:04 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How can I lookup when match has more than one value?

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?



  #4  
Old July 24th, 2007, 04:18 PM posted to microsoft.public.excel.worksheet.functions
bonot1
external usenet poster
 
Posts: 4
Default How can I lookup when match has more than one value?

This is helpful and I will think on this, but in my case nearly all of the
CountIfs will be greater than 1. To use your example, I may have 10
instances of "Joe" in my list; the lookup against "Joe" might return "abc" or
"cde". Most Joe, Pam, etc. have only one value "abc" as their match, but
some Joe, Pam, etc. have both "abc" and "cde". I need a way to know that.

"David Hilberg" wrote:

=IF( CountIf(a1:a10,"Joe")1, CountIf(a1:a10,"Joe"),
LookUp(..etc...) )

will give you the count if there are more or fewer than one.
Otherwise, it performs the lookup.

- David

On Jul 23, 9:12 pm, bonot1 wrote:
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?




  #5  
Old July 24th, 2007, 04:18 PM posted to microsoft.public.excel.worksheet.functions
bonot1
external usenet poster
 
Posts: 4
Default How can I lookup when match has more than one value?

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?




  #6  
Old July 24th, 2007, 07:14 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How can I lookup when match has more than one value?

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?






  #7  
Old July 25th, 2007, 01:38 AM posted to microsoft.public.excel.worksheet.functions
bonot1
external usenet poster
 
Posts: 4
Default How can I lookup when match has more than one value?

Thanks. I am working with your suggestion, but I am not sure if I expressed
my problem clearly.

Using your example, in A2:A20 there would be say three different values
"abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for every
"abc", a "345" for every "cde"; VLOOKUP works fine for these. However, rows
with "efg" in column A sometimes have "789" in column B and sometimes have
"567".

What I need is to 1) be made aware that "efg" has two different matches in
column B, and 2) know what the values of those two matches are. This is what
I would like to automate.

"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?






  #8  
Old July 25th, 2007, 03:26 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default How can I lookup when match has more than one value?

Well, then all you need to do is test for the presence of those 2 values
that correspond to "efg". I have a feeling that your sample data is "fake"
so any formula I suggest might not work on your real data since the formula
is based on your explanation. Anyhow, try this:

=IF(COUNTIF(A$2:A$20,"efg"),IF(AND(COUNTIF(B$2:B$2 0,567),COUNTIF(B$2:B$20,789)),INDEX({567,789},ROWS ($1:1)),IF(ROWS($1:1)=1,VLOOKUP("efg",A$2:B$20,2,0 ),"")),"")

Copy to a total of 2 cells.

--
Biff
Microsoft Excel MVP


"bonot1" wrote in message
...
Thanks. I am working with your suggestion, but I am not sure if I
expressed
my problem clearly.

Using your example, in A2:A20 there would be say three different values
"abc", "cde", and "efg". When I lookup in B2:B20, there is a "123" for
every
"abc", a "345" for every "cde"; VLOOKUP works fine for these. However,
rows
with "efg" in column A sometimes have "789" in column B and sometimes have
"567".

What I need is to 1) be made aware that "efg" has two different matches in
column B, and 2) know what the values of those two matches are. This is
what
I would like to automate.

"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?








  #9  
Old June 15th, 2008, 01:16 PM posted to microsoft.public.excel.worksheet.functions
farhad
external usenet poster
 
Posts: 3
Default How can I lookup when match has more than one value?

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? Offcourse the data is
in random order, and the data to be returned is text. I need to have all the
records that lookup retrieve it on the same rows.

  #10  
Old June 15th, 2008, 01:18 PM posted to microsoft.public.excel.worksheet.functions
farhad
external usenet poster
 
Posts: 3
Default How can I lookup when match has more than one value?

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? Offcourse the data is
in random order, and the data to be returned is text. I need to have all the
records that lookup retrieve it on the same rows.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.