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?

Yes, there is a function that can help you with this. Instead of using the LOOKUP function, you can use the INDEX and MATCH functions together to retrieve multiple elements for one lookup value.

Here are the steps to do this:

1. Select the cell where you want to display the results.
2. Type the following formula:
   Formula:

   =INDEX(range,MATCH(lookup_value,lookup_range,0)) 

3. Replace "range" with the range of cells that contains the values you want to retrieve.
4. Replace "lookup_value" with the value you want to look up.
5. Replace "lookup_range" with the range of cells that contains the lookup values.
6. Press Ctrl+Shift+Enter to enter the formula as an array formula.

This formula will return an array of values that match the lookup value. If there are no matches, it will return an error message.

If you just want to know if there are duplicate matches, you can use the COUNTIF function. Here are the steps to do this:

1. Select the cell where you want to display the results.
2. Type the following formula:
   Formula:

   =COUNTIF(range,lookup_value) 

3. Replace "range" with the range of cells that contains the values you want to check for duplicates.
4. Replace "lookup_value" with the value you want to look up.
5. Press Enter to enter the formula.

This formula will return the number of times the lookup value appears in the range. If it returns a number greater than 1, it means there are duplicate matches.

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

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?

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?

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?

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?

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?

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?

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.

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.

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

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

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

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

A2:D10 = data table

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

=INDEX(D2:D10,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

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

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

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?

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?

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?

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?

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

Thank you so much! but i want to ask, what does 1 and 0 represents? and im a
little confused, why is it C1:C5?

Thanks you.



"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...

Thank you.



"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

Got it!!! Thank you so much for your help! Your really the best!!!

last question, if im going to add additional condition, ill just use this
formula, right?

=INDEX(Data!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1 )*(Data!B1:Data!B5=F1)*(????????????),0))

is it correct?

Thank you so much!!!

=)



"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

i still dont understand the 1 and the 0?...

MATCH(1,(A1:A5=E1)*(B1:B5=F1),0)

The 1 is the lookup_value and the 0 means we want to find an exact match of
the lookup_value.

Based on the sample I posted...

(A1:A5=E1)*(B1:B5=F1)

When both conditions are TRUE the result will be 1.

(Bob=Sue)*(x=a) = 0
(Sue=Sue)*(x=a) = 0
(Bob=Sue)*(z=a) = 0
(Sue=Sue)*(a=a) = 1
(Tom=Sue)*(h=a) = 0

This array of 1s and 0s make up the lookup_array.

MATCH(1,{0;0;0;1;0},0)

The result of MATCH is 4 and is passed to the INDEX function meaning we want
the 4th element of the indexed range C1:C5.

=INDEX(C1:C5,4)

=INDEX({"AA";"BB";"CC";"GG";"FF"},4)

GG is the 4th element of the indexed range so the result of the formula is
GG


E1 = Sue
F1 = a

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0))

=GG


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...

Thank you.



"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

Yes, but you don't need to repeat the sheet name.

=INDEX(Data!C1:C5,MATCH(1,(Data!A1:A5=E1)*(Data!B1 :B5=F1)*(????????????),0))


--
Biff
Microsoft Excel MVP


"Hijosdelongi" wrote in message
...
Got it!!! Thank you so much for your help! Your really the best!!!

last question, if im going to add additional condition, ill just use this
formula, right?

=INDEX(Data!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1 )*(Data!B1:Data!B5=F1)*(????????????),0))

is it correct?

Thank you so much!!!

=)



"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

I am currently having to set up a excel spreadsheet for a ski lodge business
and there are several issues which are very confusing.
I am using a vlookup function and it is loooking up and selecting text, but
it is selecting the first text only because ther are several different values
with the same value, but i would rather it display more than one answer. is
there a variation of the vlookup function which shows two possible answers or
is there another function which is better suited??


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

I have a flowup question: how can I return all of match up cells in my
spreadsheet?

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

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?

I'm not sure how to word this exactly, but I have one sheet like this:

a b c d
red 12 11 9
blue 9 4 7
yellow 21 16 5
red 3 13 11

And I want to return the rows of just 'red' entries on another sheet,
without blank rows in the new sheet, like this:

a b c d
red 12 11 9
red 3 13 11

Can you help?

So I can help can you advise re the following:

1) Is there a fixed number of values you want to display, or does it vary?
2) How would you want them displayed? in a column? in a row?

Let me know the above and I'll post you the solution,

Jay
__


findlay wrote:
I am currently having to set up a excel spreadsheet for a ski lodge business
and there are several issues which are very confusing.
I am using a vlookup function and it is loooking up and selecting text, but
it is selecting the first text only because ther are several different values
with the same value, but i would rather it display more than one answer. is
there a variation of the vlookup function which shows two possible answers or
is there another function which is better suited??


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

Hi,
May I request how to use vlookup or Index/Match combination to lookup the
drive utilization, for same hostname..

Server1 C:\ (2) 21478666240 17488568534 3990097706 81.42297263

Server1 D:\ (3) 2.9362E+11 68383018334 2.25237E+11 23.28963077


Server1 E:\ (4) 1.25325E+11 48705869576 76619192568 38.8636309

With Vlookup or Index/Match basic function use, I can get either the 1st
match or the last match, but not the middle one or the drive D. Any clue is
much appreciated.
Thankyou


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

Hi Valko,

I have something similar and I am just used to vlookup for one single value
to compare two columns so I get value in column2 returned.

Now I do have two columns plus a condition and three columns, and I would
like to get the value in column3 returned.

example:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
in column G.

Result: Cell D2 should get value "10" (G3) and Cell D3 should get the value
"20" (G2).

Thank you so much in advance.

Regards, Timo


"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

Try something this:

=SUMPRODUCT(--(E$2:E$10=A2),--(F$2:F$10=B2),G$2:G$10)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Timo" wrote in message
...
Hi Valko,

I have something similar and I am just used to vlookup for one single
value
to compare two columns so I get value in column2 returned.

Now I do have two columns plus a condition and three columns, and I would
like to get the value in column3 returned.

example:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
in column G.

Result: Cell D2 should get value "10" (G3) and Cell D3 should get the
value
"20" (G2).

Thank you so much in advance.

Regards, Timo


"T. Valko" wrote:

Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),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
...
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?

Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.

=sumif(a:a,"B",c:c)

I changed the string to B just so there wouldn't be so many A's in the formula.



Ray wrote:

Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.

--

Dave Peterson

Hi, I have the same query i.e. sum of multiple vlookups....I'd be very
interested to get advice

"Ray" wrote:

Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.

I had similar issue and surfed this website to find the solution but then I
trued used SumIF and it worked for me. For you it shoould be sumif(A1:A8, a,
C1:C8). Hope it works for you as well. Good luck.

Cheers,
Abid

"Dave Peterson" wrote:

=sumif(a:a,"B",c:c)

I changed the string to B just so there wouldn't be so many A's in the formula.



Ray wrote:

Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.

--

Dave Peterson

That should just be a simple sumif:
=SUMIF($A$1:$C$8,"a",$C$1:$C$8)

"Ray" wrote:

Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.