|
|
|
|
|
|
|
|
|
|
| |
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 <bon...@discussions.microsoft.com> 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?
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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 <bon...@discussions.microsoft.com> 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?
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> 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 <bon...@discussions.microsoft.com> 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?
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> > 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 <bon...@discussions.microsoft.com> 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?
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
>> > 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 <bon...@discussions.microsoft.com> 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?
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> >> > 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 <bon...@discussions.microsoft.com> 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> >> > 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 <bon...@discussions.microsoft.com> 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
>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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:62BD29FC-470A-4873-AD42-332E798DC6C2@microsoft.com...
> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
>> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in
>> >> message
>> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
>> >> > 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 <bon...@discussions.microsoft.com>
>> >> >> 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1)*(Data!B1:Data!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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> >> > 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 <bon...@discussions.microsoft.com> 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
news:166CE749-FBDB-4190-80BA-A5EF8DEF6074@microsoft.com...
> 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!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1)*(Data!B1:Data!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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
>> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in
>> >> message
>> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
>> >> > 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 <bon...@discussions.microsoft.com>
>> >> >> 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
> >> > 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 <bon...@discussions.microsoft.com> 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Timo@discussions.microsoft.com> wrote in message
news:A820E820-BBF8-4394-BE0E-59293CF5D309@microsoft.com...
> 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" <Hijosdelongi@discussions.microsoft.com> wrote in message
>> news:3D38CD88-BAC1-4C17-B9FE-F6E3FC45FA03@microsoft.com...
>> > 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" <Hijosdelongi@discussions.microsoft.com> wrote in
>> >> message
>> >> news:0FDAB9E7-EDEE-4CBE-A802-C6CC4DB19A53@microsoft.com...
>> >> > 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 <bon...@discussions.microsoft.com>
>> >> >> 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies