|
How can I lookup when match has more than one value?
Hi Ray,
You may use the SUMIF function: =SUMIF(A:A, "a", C:C) Have a great day! |
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! |
How can I lookup when match has more than one value?
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. |
How can I lookup when match has more than one value?
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? |
How can I lookup when match has more than one value
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 |
How can I 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. |
Please help me to solve the below equation in excel
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 |
How can I lookup 2 values in order to return 1?
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? |
How can I lookup 2 values in order to return 1?
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? |
How can I lookup when match has more than one value?
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! |
How can I lookup when match has more than one value?
"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... ?? |
How can I lookup when match has more than one value?
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? |
How can I lookup when match has more than one value?
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 |
How can I lookup when match has more than one value?
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 |
How can I lookup when match has more than one value?
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 . |
How can I lookup when match has more than one value?
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 . |
| All times are GMT +1. The time now is 05:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com