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

Hi Ray,

Use the SUMIF function:

=SUMIF(A:A, "a", C:C)

Have a great day!

Try the SUMIF function. It worked for me.


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

Is there a way to drag this formula and not enter the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER) for every row?

This formula works great for my worksheet but I need to drag it down 10,000+
rows?

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



"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
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1



Date Item Qty Item Stock
1-Jan a 12 a 14
4-Mar b 13 b 15
4-May c 14 c 16
7-Sep d 15 d 17
8-Mar e 16 e 18
9-Aug f 17 f 19
12-Dec g 17 g 19
12-Dec a 1

Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this

Hi
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1

Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17


Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19


Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this.

Hi

The equation

if item_a=stockitem_a
(stockqty_a=stockqty_a-itemqty_a)
else if item_b=stockitem_b
(stockqty_b=stockqty_b-itemqty_b)
....
but more than 7 nested loops are not applicable. so please suggest.
....
when i enter an item, the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...
eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1

Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17

Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19



Waiting for the suggestions. we cannot use the more than 7 nested loops.
waiting for the solution with thanks

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vas" wrote in message
...
Hi

The equation

if item_a=stockitem_a
(stockqty_a=stockqty_a-itemqty_a)
else if item_b=stockitem_b
(stockqty_b=stockqty_b-itemqty_b)
...
but more than 7 nested loops are not applicable. so please suggest.
...
when i enter an item, the stock should be reduced by the quantity and whn
i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...
eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1

Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17

Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19



Waiting for the suggestions. we cannot use the more than 7 nested loops.
waiting for the solution with thanks

Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to
lookup 2 different dates in a table and if both of those dates appear on the
one sheet, I need the associated value to be returned. If this is possible,
how would I do this?

Use this array formula

=INDEX(rng3,MATCH(1,(rng1=condition1)*(rng2=condit ion2),0))

--

HTH

Bob

"Sarah" wrote in message
...
Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to
lookup 2 different dates in a table and if both of those dates appear on
the
one sheet, I need the associated value to be returned. If this is
possible,
how would I do this?

I had a question re duplicates as well:
I am trying to do a vlookup matching number figurers to their corresponding
year. However the years are duplicated:

Jan 1990 5555

feb 1990 4555

Mar 1990 4444

Jan 1991 54487

Feb 1991 2255

And so on...

i have a separate table with just the numbers. I am trying to match the
year to the numbers in the other table but am uable to use the vlookp due to
the duplicates.

Suggestions would be great!

Thanks!