If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#32
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#33
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#34
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#35
|
|||
|
|||
How can I lookup when match has more than one value?
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. |
#36
|
|||
|
|||
How can I lookup when match has more than one value?
=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 |
#37
|
|||
|
|||
How can I lookup when match has more than one value?
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. |
#38
|
|||
|
|||
How can I lookup when match has more than one value?
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 |
#39
|
|||
|
|||
How can I lookup when match has more than one value?
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. |
#40
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|