How do I select a value from a table based on 2 conditional refs?

I'm trying to select a value from a table based on 2 variable references.

If been looking at using a VLOOKUP function to do this but because there are
2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
with a MATCH function incorporated might be an option as the entries in each
column are not unique.

To give you an example of what I am trying to achieve I done the table below.

Basically if F1=9 & G2=2 then select C1 (*)

A B C D F G
1 9 3 * % 9 2
2 9 2 % &
3 9 1 # *
4 7 3 * %

Thanks

Hi

For your example try:
=SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4))
I'm presuming (by the way) that your example is wrong, as I would have
expected it to return %.
When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges
used must be the same size.

Hope this helps.
Andy.

"Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in message
news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
> I'm trying to select a value from a table based on 2 variable references.
>
> If been looking at using a VLOOKUP function to do this but because there
> are
> 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
> with a MATCH function incorporated might be an option as the entries in
> each
> column are not unique.
>
> To give you an example of what I am trying to achieve I done the table
> below.
>
> Basically if F1=9 & G2=2 then select C1 (*)
>
> A B C D F G
> 1 9 3 * % 9 2
> 2 9 2 % &
> 3 9 1 # *
> 4 7 3 * %
>
> Thanks

Thanks for your response Andy. Sorry, my example did have a mistake in it.

My actual data contains dates which I should have mentioned in my original
email. I don't think that this function would work because of that. I need
to find a way to search for a date which matches with a value (eg: 50) which
then okays the further selection of another specified date in the same table.

Thanks for your feedback. Any further thoughts would be appreciated.

Puzzled Percy

"Andy" wrote:

> Hi
>
> For your example try:
> =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4))
> I'm presuming (by the way) that your example is wrong, as I would have
> expected it to return %.
> When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges
> used must be the same size.
>
> Hope this helps.
> Andy.
>
> "Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in message
> news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
> > I'm trying to select a value from a table based on 2 variable references.
> >
> > If been looking at using a VLOOKUP function to do this but because there
> > are
> > 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
> > with a MATCH function incorporated might be an option as the entries in
> > each
> > column are not unique.
> >
> > To give you an example of what I am trying to achieve I done the table
> > below.
> >
> > Basically if F1=9 & G2=2 then select C1 (*)
> >
> > A B C D F G
> > 1 9 3 * % 9 2
> > 2 9 2 % &
> > 3 9 1 # *
> > 4 7 3 * %
> >
> > Thanks
>
>
>

Hi

There is no reason why this format of formula will not work with dates - as
you want. If you post some more accurate data I'll have a go!

Andy.

"Puzzled Percy" <PuzzledPercy@discussions.microsoft.com> wrote in message
news:0B5A68B9-0AC6-410B-8375-DB3C2D7AABC7@microsoft.com...
> Thanks for your response Andy. Sorry, my example did have a mistake in
> it.
>
> My actual data contains dates which I should have mentioned in my original
> email. I don't think that this function would work because of that. I
> need
> to find a way to search for a date which matches with a value (eg: 50)
> which
> then okays the further selection of another specified date in the same
> table.
>
> Thanks for your feedback. Any further thoughts would be appreciated.
>
> Puzzled Percy
>
> "Andy" wrote:
>
>> Hi
>>
>> For your example try:
>> =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4))
>> I'm presuming (by the way) that your example is wrong, as I would have
>> expected it to return %.
>> When using SUMPRODUCT, you can't use full column ranges (A:A) and the
>> ranges
>> used must be the same size.
>>
>> Hope this helps.
>> Andy.
>>
>> "Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in
>> message
>> news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
>> > I'm trying to select a value from a table based on 2 variable
>> > references.
>> >
>> > If been looking at using a VLOOKUP function to do this but because
>> > there
>> > are
>> > 2 dependant references I'm a little stuck. I thought that maybe a
>> > VLOOKUP
>> > with a MATCH function incorporated might be an option as the entries in
>> > each
>> > column are not unique.
>> >
>> > To give you an example of what I am trying to achieve I done the table
>> > below.
>> >
>> > Basically if F1=9 & G2=2 then select C1 (*)
>> >
>> > A B C D F G
>> > 1 9 3 * % 9 2
>> > 2 9 2 % &
>> > 3 9 1 # *
>> > 4 7 3 * %
>> >
>> > Thanks
>>
>>
>>

=INDEX(C1:C4,MATCH(1,(A1:A4=F1)*(B1:B4=G1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in message
news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
> I'm trying to select a value from a table based on 2 variable references.
>
> If been looking at using a VLOOKUP function to do this but because there
are
> 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
> with a MATCH function incorporated might be an option as the entries in
each
> column are not unique.
>
> To give you an example of what I am trying to achieve I done the table
below.
>
> Basically if F1=9 & G2=2 then select C1 (*)
>
> A B C D F G
> 1 9 3 * % 9 2
> 2 9 2 % &
> 3 9 1 # *
> 4 7 3 * %
>
> Thanks

Hi Andy,

I should have tried your fomula first before doubting you! (Won't do that
again) It works great for what I want it to do given the way that Excel
works out dates.

You are fantastic

Regards,

Puzzled Percy

"Andy" wrote:

> Hi
>
> There is no reason why this format of formula will not work with dates - as
> you want. If you post some more accurate data I'll have a go!
>
> Andy.
>
> "Puzzled Percy" <PuzzledPercy@discussions.microsoft.com> wrote in message
> news:0B5A68B9-0AC6-410B-8375-DB3C2D7AABC7@microsoft.com...
> > Thanks for your response Andy. Sorry, my example did have a mistake in
> > it.
> >
> > My actual data contains dates which I should have mentioned in my original
> > email. I don't think that this function would work because of that. I
> > need
> > to find a way to search for a date which matches with a value (eg: 50)
> > which
> > then okays the further selection of another specified date in the same
> > table.
> >
> > Thanks for your feedback. Any further thoughts would be appreciated.
> >
> > Puzzled Percy
> >
> > "Andy" wrote:
> >
> >> Hi
> >>
> >> For your example try:
> >> =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4))
> >> I'm presuming (by the way) that your example is wrong, as I would have
> >> expected it to return %.
> >> When using SUMPRODUCT, you can't use full column ranges (A:A) and the
> >> ranges
> >> used must be the same size.
> >>
> >> Hope this helps.
> >> Andy.
> >>
> >> "Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in
> >> message
> >> news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
> >> > I'm trying to select a value from a table based on 2 variable
> >> > references.
> >> >
> >> > If been looking at using a VLOOKUP function to do this but because
> >> > there
> >> > are
> >> > 2 dependant references I'm a little stuck. I thought that maybe a
> >> > VLOOKUP
> >> > with a MATCH function incorporated might be an option as the entries in
> >> > each
> >> > column are not unique.
> >> >
> >> > To give you an example of what I am trying to achieve I done the table
> >> > below.
> >> >
> >> > Basically if F1=9 & G2=2 then select C1 (*)
> >> >
> >> > A B C D F G
> >> > 1 9 3 * % 9 2
> >> > 2 9 2 % &
> >> > 3 9 1 # *
> >> > 4 7 3 * %
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
>

Thanks Bob,

Your formula works great too! This was more what I had in mind when I first
posted as I will be able to use text with it in a different scenario. You
are FANTASTIC

Regards,

Puzzled Percy

"Bob Phillips" wrote:

> =INDEX(C1:C4,MATCH(1,(A1:A4=F1)*(B1:B4=G1),0))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Puzzled Percy" <Puzzled Percy@discussions.microsoft.com> wrote in message
> news:A7B0BB9D-4407-467C-AAAA-46E79DE2AD27@microsoft.com...
> > I'm trying to select a value from a table based on 2 variable references.
> >
> > If been looking at using a VLOOKUP function to do this but because there
> are
> > 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP
> > with a MATCH function incorporated might be an option as the entries in
> each
> > column are not unique.
> >
> > To give you an example of what I am trying to achieve I done the table
> below.
> >
> > Basically if F1=9 & G2=2 then select C1 (*)
> >
> > A B C D F G
> > 1 9 3 * % 9 2
> > 2 9 2 % &
> > 3 9 1 # *
> > 4 7 3 * %
> >
> > Thanks
>
>
>