Lookup with multiple conditions

G'day all,

I was just wondering if there was a way to return a value from a data table by specifying 3 conditions to be met.

Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)

I want to be able to return the time value, based on track, distance and class values.

Thanks in advance,

Sven

You might look into the DCOUNT / DSUM functions.
Or check Bob's site for use of SUMPRODUCT for multi-condition tests:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--Bruce

"Svenvlad" wrote:

>
> G'day all,
>
> I was just wondering if there was a way to return a value from a data
> table by specifying *3* conditions to be met.
>
> Eg. My data table is in cells W1:Z100
> (Column names = Track, Distance, Class, Time)
>
> I want to be able to return the time value, based on track, distance
> and class values.
>
> Thanks in advance,
>
> Sven
>
>
> --
> Svenvlad
> ------------------------------------------------------------------------
> Svenvlad's Profile: http://www.excelforum.com/member.php...o&userid=28916
> View this thread: http://www.excelforum.com/showthread...hreadid=490493
>
>

Thanks a lot for the suggestions,

I'll look into them both,

Sven

Hi!

One way:

=INDEX(Z1:Z100,MATCH(1,(W1:W100="whatever")*(X1:X100="whatever")*(Y1:Y100="whatever"),0))

This is an array formula and must be entered using the key combo of
CTRL,SHIFT,ENTER.

Replace "whatever" with the appropriate variable criteria.

Text variables must be enclosed in quotes: "whatever"
Number variables should not be enclosed in quotes: 10

Better if you use cells to hold the criteria and then refer to those cells:

=INDEX(Z1:Z100,MATCH(1,(W1:W100=AA1)*(X1:X100=AA2)*(Y1:Y100=AA3),0))

Biff

"Svenvlad" <[email protected]> wrote in
message news:[email protected]...
>
> G'day all,
>
> I was just wondering if there was a way to return a value from a data
> table by specifying *3* conditions to be met.
>
> Eg. My data table is in cells W1:Z100
> (Column names = Track, Distance, Class, Time)
>
> I want to be able to return the time value, based on track, distance
> and class values.
>
> Thanks in advance,
>
> Sven
>
>
> --
> Svenvlad
> ------------------------------------------------------------------------
> Svenvlad's Profile:
> http://www.excelforum.com/member.php...o&userid=28916
> View this thread: http://www.excelforum.com/showthread...hreadid=490493
>

Biff,

Thanks heaps, worked perfectly.

You're a legend...

Sven

Best, why? Since it is a numeric value that is being returned I would go so
far and say this is a better method

=SUMPRODUCT(--(range1=criteria1),--(range2=criteria2),--(range3=criteria3),time_range)

entered normally

offset is a volatile function so I try to avoid it if there are other
methods, another method if the time value is text that is "better" than
offset would be

=INDEX(Time_Range,MATCH(1,(range1=criteria1)*(range1=criteria1)*(range1=criteria1),0))

array entered

--
Regards,

Peo Sjoblom

(No private emails please)


"duane" <[email protected]> wrote in
message news:[email protected]...
>
> this question came up recently and the best solution is an array
> formula:
>
> of the form
>
> =offset(X,match(1,(range 1 = critieria 1)*(range 2 = critiera 2)*(range
> 3 = criteria 3)),0)
>
> entered with control+shift+enter
>
> where X is the top cell in the column desired to locate the data point
> from which you abd range 1-3 are the ranges in which you want to find
> the matches of the criterias
>
>
> --
> duane
>
>
> ------------------------------------------------------------------------
> duane's Profile:
> http://www.excelforum.com/member.php...o&userid=11624
> View this thread: http://www.excelforum.com/showthread...hreadid=490493
>

You're welcome. Thanks for the feedback.

Also see Peo's suggestion using Sumproduct. It is the "best" method to use
if the value being returned is a number.

Biff

"Svenvlad" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> Thanks heaps, worked perfectly.
>
> You're a legend...
>
> Sven
>
>
> --
> Svenvlad
> ------------------------------------------------------------------------
> Svenvlad's Profile:
> http://www.excelforum.com/member.php...o&userid=28916
> View this thread: http://www.excelforum.com/showthread...hreadid=490493
>

Try this and use 3 conditions (A&B&C) instead of two.
:
By: Bob Phillips In: microsoft.public.excel.worksheet.functions


=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000&Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

BTW, here is what I did:

=INDEX($G$2:$P$1137,MATCH(A23&E23,$H$2:$H$1137&$I$2:$I$1137,0),6)

where the result I want is in the 6th column of $G$2:$P$1137, A23 and E23
are the values I am trying to match with values somewhere in columns H and I,
respectively, and I require an exact match (0).

Thanks Bob and Dave, and others.




"Svenvlad" wrote:

>
> G'day all,
>
> I was just wondering if there was a way to return a value from a data
> table by specifying *3* conditions to be met.
>
> Eg. My data table is in cells W1:Z100
> (Column names = Track, Distance, Class, Time)
>
> I want to be able to return the time value, based on track, distance
> and class values.
>
> Thanks in advance,
>
> Sven
>
>
> --
> Svenvlad
> ------------------------------------------------------------------------
> Svenvlad's Profile: http://www.excelforum.com/member.php...o&userid=28916
> View this thread: http://www.excelforum.com/showthread...hreadid=490493
>
>

Hello, this is not working in my spreadsheet. I want to return the value in
'Shots' Column U by matching the value of B7 in 'Shots' column W AND matching
value of C7 in 'Shots' column AA to return the row value. Please note that
'Shots' is another spreadsheet. This is what I have and it is returning VALUE!

=INDEX(SHOTS!U:U,MATCH(B7&C7,SHOTS!W:W&SHOTS!AA:AA,0))

Any ideas?

"cbuker" wrote:

> Try this and use 3 conditions (A&B&C) instead of two.
> :
> By: Bob Phillips In: microsoft.public.excel.worksheet.functions
>
>
> =INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000&Sheet2!B1:B1000,0))
>
> as an array formula, so commit with Ctrl-Shift-Enter
>
> BTW, here is what I did:
>
> =INDEX($G$2:$P$1137,MATCH(A23&E23,$H$2:$H$1137&$I$2:$I$1137,0),6)
>
> where the result I want is in the 6th column of $G$2:$P$1137, A23 and E23
> are the values I am trying to match with values somewhere in columns H and I,
> respectively, and I require an exact match (0).
>
> Thanks Bob and Dave, and others.
>
>
>
>
> "Svenvlad" wrote:
>
> >
> > G'day all,
> >
> > I was just wondering if there was a way to return a value from a data
> > table by specifying *3* conditions to be met.
> >
> > Eg. My data table is in cells W1:Z100
> > (Column names = Track, Distance, Class, Time)
> >
> > I want to be able to return the time value, based on track, distance
> > and class values.
> >
> > Thanks in advance,
> >
> > Sven
> >
> >
> > --
> > Svenvlad
> > ------------------------------------------------------------------------
> > Svenvlad's Profile: http://www.excelforum.com/member.php...o&userid=28916
> > View this thread: http://www.excelforum.com/showthread...hreadid=490493
> >
> >