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
  #11  
Old June 15th, 2008, 01:38 PM posted to microsoft.public.excel.worksheet.functions
Niek Otten
external usenet poster
 
Posts: 2,533
Default How can I lookup when match has more than one value?

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

Unfortunately, the formulas are truncated. But the text explains very well what to do, so hopefully you can manage.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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


  #12  
Old June 15th, 2008, 07:26 PM posted to microsoft.public.excel.worksheet.functions
Alan Beban[_2_]
external usenet poster
 
Posts: 81
Default How can I lookup when match has more than one value?

farhad 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? 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.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you might
want to consider the VLookups function. It is designed for this purpose.

Alan Beban
  #13  
Old October 3rd, 2008, 03:25 AM posted to microsoft.public.excel.worksheet.functions
Alfonso Valdes
external usenet poster
 
Posts: 1
Default How can I lookup when match has more than one value?

Hi I have a huge list of data that has items and each item has different
specifications. For example:


36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
The output that this vlookup will give me would be "Z28031-1" but in some
cases I want the information of the second row"5313312" or maybe the
third"4758766".
I have seen that there is explanations, and formulas that give you all the
info like this:
34377007 Z28031-1
5313312
4758766
But for the purpose of what i am doing I do not need all the values I just
need one of them.


for example:

Same vlookup, but I want the formula to give me the info from the second row
when it found the first value that match the vlookup

I do not know if exist a formula that makes this
vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
=“5313312”

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200






Same vlookup, but I want the formula to give me the info from the third row
when it found the first value that match the vlookup.
Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
=“4758766”

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200


  #14  
Old October 3rd, 2008, 04:07 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?

If your data table is sorted or grouped together as is shown in your sample:

A210 = data table

F2 = lookup value = 34377007
G2 = instance number = 2

=INDEX(D210,MATCH(F2,A2:A10,0)+G2-1)

Result = 5313312

--
Biff
Microsoft Excel MVP


"Alfonso Valdes" Alfonso wrote in message
...
Hi I have a huge list of data that has items and each item has different
specifications. For example:


36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
The output that this vlookup will give me would be "Z28031-1" but in some
cases I want the information of the second row"5313312" or maybe the
third"4758766".
I have seen that there is explanations, and formulas that give you all the
info like this:
34377007 Z28031-1
5313312
4758766
But for the purpose of what i am doing I do not need all the values I
just
need one of them.


for example:

Same vlookup, but I want the formula to give me the info from the second
row
when it found the first value that match the vlookup

I do not know if exist a formula that makes this
vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
="5313312"

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200






Same vlookup, but I want the formula to give me the info from the third
row
when it found the first value that match the vlookup.
Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
="4758766"

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200




  #15  
Old October 13th, 2008, 10:22 PM posted to microsoft.public.excel.worksheet.functions
MRLYONS68
external usenet poster
 
Posts: 1
Default How can I lookup when match has more than one value?

I have also tried to use this article, but I am getting "#NUM" error values
using the "SMALL" mixed with the "ROW" functions. I am following the example
verbatim, and I do not think this should be that difficult. This article
attempts to do EXACTLY what I am looking for.

Does anyone know of where the actual example can be downloaded as some of
the formulas are truncated?

Regards,

Mike

"Niek Otten" wrote:

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

Unfortunately, the formulas are truncated. But the text explains very well what to do, so hopefully you can manage.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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



  #16  
Old October 13th, 2008, 10:29 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom[_2_]
external usenet poster
 
Posts: 963
Default How can I lookup when match has more than one value?

I don't know what article you are talking about? I have an example that can
be downloaded
from here


http://www.nwexcelsolutions.com/advanced_function_page.htm#6._You_can_use_a_combin ation_of_functions_to_return_more_than_one_value,_ with_the_lookup_value_in_A1_and_the_lookup_table__ in_First!$A$1:$B$20_where_we_want_to_return_the_va lues_in_B1:B20


here's the direct link below




http://nwexcelsolutions.com/Download...20lookup .xls

--


Regards,


Peo Sjoblom

"MRLYONS68" wrote in message
...
I have also tried to use this article, but I am getting "#NUM" error values
using the "SMALL" mixed with the "ROW" functions. I am following the
example
verbatim, and I do not think this should be that difficult. This article
attempts to do EXACTLY what I am looking for.

Does anyone know of where the actual example can be downloaded as some of
the formulas are truncated?

Regards,

Mike

"Niek Otten" wrote:

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

Unfortunately, the formulas are truncated. But the text explains very
well what to do, so hopefully you can manage.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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





  #17  
Old July 13th, 2009, 03:08 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default How can I lookup when match has more than one value?

Hi,

I have a Question.. if VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

Can you help me with this..

THank you.


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




  #18  
Old July 13th, 2009, 03:34 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default How can I lookup when match has more than one value?

Hi,

I have a Question.. is VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

is my logical tests or conditions correct? and how will i put the VLOOKUP
codes?

Can you help me with this..

THank you so much


"T. Valko" wrote:

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?









  #19  
Old July 13th, 2009, 04:08 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?

Where is the data you want returned?

What is the data type of the value to be returned? Is it text? Numeric?

When there are multiple lookup_values you would typically use an array
formula** like this:

=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data! B1:B10=D1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a Question.. if VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

Can you help me with this..

THank you.


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






  #20  
Old July 13th, 2009, 04:54 AM posted to microsoft.public.excel.worksheet.functions
Hijosdelongi
external usenet poster
 
Posts: 33
Default How can I lookup when match has more than one value?

Actually the data that i want to be returned is both numeric and text thats
why i had IF function.. in a typical VLOOKUP formula is should be like this..

=VLOOKUP(B1,Data!A1:A1000,2,FALSE)

but since i only want to have the data that is equivalent to the date and
the name on the database thats why im to use this formula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

that is also why im having problems in how to formulate the conditions in
the IF function and how to get the data using the VLOOKUP.

I hope you can help me.

Thank you so much.




"T. Valko" wrote:

Where is the data you want returned?

What is the data type of the value to be returned? Is it text? Numeric?

When there are multiple lookup_values you would typically use an array
formula** like this:

=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data! B1:B10=D1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Hi,

I have a Question.. if VLOOKUP plus IF possible? This is my fomula..

=VLOOKUP(IF((Data!A1:A1000=A1)*(Data!B1:B1000=D1), ....

Can you help me with this..

THank you.


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






 




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 08:35 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.