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

[Majal]

Hi Ray,

Use the SUMIF function:

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

Have a great day!

 

[wjay29]

Try the SUMIF function. It worked for me.

 

[Cstep]

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

 

[vas]

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

 

[vas]

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.

 

[vas]

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

 

[Don Guillett]

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.

 

[DP]

The original question is close to what I'm looking for. I have two pages in
a workbook - SOURCE & DATA. SOURCE has a single column of information.
DATA has multiple columns, can match SOURCE on column 1, but also have
multiple matches for each.

SOURCE Worksheet
Col A
1 abc
2 def
3 ghi
4 jkl
5 mno

DATA Worksheet
Col A B C
1 ghi 12 1000 E. Main
2 mno 09 12W23S Western
3 abc 43 400 3A Crawford
4 abc 05 1313 Mockingbird Lane
5 mno 23 666 Damian Way

Doing a normal VLookup (in the SOURCE worksheet) would return the first
match (SOURCE Row 1, to DATA Row 3). I need to write the formula so that it
returns DATA Columns A, B, C for Rows 3 and 4.

SOURCE
Row 2 = 0 matches
Row 3 = 1 match
Row 4 = 0 matches
Row 5 = 2 matches

Please advise. Thank you!

 

[Sarah]

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?

 

[Bob Phillips]

Use this array formula

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

 

[Minnie]

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!

 

[The Rimalaya]

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

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

 

[Gemini...JV]

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?

 

[G.]

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

 

[ozgrid.com]

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

 

[G.]

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.

 

[Roger Govier]

Hi

Drag B to the Column area