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 |
|
#1
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#2
|
|||
|
|||
How can I lookup when match has more than one value?
=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? |
#3
|
|||
|
|||
How can I lookup when match has more than one value?
This is helpful and I will think on this, but in my case nearly all of the
CountIfs will be greater than 1. To use your example, I may have 10 instances of "Joe" in my list; the lookup against "Joe" might return "abc" or "cde". Most Joe, Pam, etc. have only one value "abc" as their match, but some Joe, Pam, etc. have both "abc" and "cde". I need a way to know that. "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? |
#4
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#5
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#6
|
|||
|
|||
How can I lookup when match has more than one value?
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|