Hi Ray,

You may use the SUMIF function:

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

Have a great day!

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!

"T. Valko" wrote:

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





Cann't we do the same thing, if the data are not sorted... ??

I am having trouble with the following:

I have a sheet sorted in ID order and they have reported months next to them
(therefore there are duplicates ID).

Data source
2138 january 147
2138 february 161
2138 may 112
2138 june 191
2384 january 118
2384 february 119

New report
January february march april
2138
2384

I want to find a fuction where I need the 3rd column from source with ID and
Month matching.

Can you please help?


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

Hi I actually have another similar problem, can you help me?

Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column. Please
help!


1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
Hi I actually have another similar problem, can you help me?

Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column.
Please
help!


1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888

Hi,
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?

Thanks,
G.



"ozgrid.com" wrote:

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
Hi I actually have another similar problem, can you help me?

Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column.
Please
help!


1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888

.

Hi

Drag B to the Column area
--
Regards
Roger Govier

G. wrote:
Hi,
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?

Thanks,
G.



"ozgrid.com" wrote:

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
Hi I actually have another similar problem, can you help me?

Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column.
Please
help!


1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888
.