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 |
#21
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#22
|
|||
|
|||
How can I lookup when match has more than one value?
Thank you so much! but i want to ask, what does 1 and 0 represents? and im a
little confused, why is it C1:C5? Thanks you. "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? |
#23
|
|||
|
|||
How can I lookup when match has more than one value?
IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...
Thank you. "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? |
#24
|
|||
|
|||
How can I lookup when match has more than one value?
Got it!!! Thank you so much for your help! Your really the best!!!
last question, if im going to add additional condition, ill just use this formula, right? =INDEX(Data!C1ata!C5,MATCH(1,(Data!A1ata!A5=E1 )*(Data!B1ata!B5=F1)*(????????????),0)) is it correct? Thank you so much!!! =) "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? |
#25
|
|||
|
|||
How can I lookup when match has more than one value?
i still dont understand the 1 and the 0?...
MATCH(1,(A1:A5=E1)*(B1:B5=F1),0) The 1 is the lookup_value and the 0 means we want to find an exact match of the lookup_value. Based on the sample I posted... (A1:A5=E1)*(B1:B5=F1) When both conditions are TRUE the result will be 1. (Bob=Sue)*(x=a) = 0 (Sue=Sue)*(x=a) = 0 (Bob=Sue)*(z=a) = 0 (Sue=Sue)*(a=a) = 1 (Tom=Sue)*(h=a) = 0 This array of 1s and 0s make up the lookup_array. MATCH(1,{0;0;0;1;0},0) The result of MATCH is 4 and is passed to the INDEX function meaning we want the 4th element of the indexed range C1:C5. =INDEX(C1:C5,4) =INDEX({"AA";"BB";"CC";"GG";"FF"},4) GG is the 4th element of the indexed range so the result of the formula is GG E1 = Sue F1 = a =INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0)) =GG -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?... Thank you. "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? |
#26
|
|||
|
|||
How can I lookup when match has more than one value?
Yes, but you don't need to repeat the sheet name.
=INDEX(Data!C1:C5,MATCH(1,(Data!A1:A5=E1)*(Data!B1 :B5=F1)*(????????????),0)) -- Biff Microsoft Excel MVP "Hijosdelongi" wrote in message ... Got it!!! Thank you so much for your help! Your really the best!!! last question, if im going to add additional condition, ill just use this formula, right? =INDEX(Data!C1ata!C5,MATCH(1,(Data!A1ata!A5=E1 )*(Data!B1ata!B5=F1)*(????????????),0)) is it correct? Thank you so much!!! =) "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? |
#27
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#28
|
|||
|
|||
How can I lookup when match has more than one value?
I have a flowup question: how can I return all of match up cells in my
spreadsheet? "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? |
#29
|
|||
|
|||
How can I lookup when match has more than one value?
This is very helpful to me also; however, I have a need to return every match
to a specific cell or in a continuous stream in one cell. The matches are names that match a particular number. Is there a way to print in columns specified, each match to the number being looked up? "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? |
#30
|
|||
|
|||
How can I lookup when match has more than one value?
I'm not sure how to word this exactly, but I have one sheet like this:
a b c d red 12 11 9 blue 9 4 7 yellow 21 16 5 red 3 13 11 And I want to return the rows of just 'red' entries on another sheet, without blank rows in the new sheet, like this: a b c d red 12 11 9 red 3 13 11 Can you help? |
Thread Tools | |
Display Modes | |
|
|