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

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

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

Thanks a lot for the suggestions,

I'll look into them both,

Sven


--
Svenvlad
------------------------------------------------------------------------
Svenvlad's Profile: http://www.excelforum.com/member.php...o&userid=28916
View this thread: http://www.excelforum.com/showthread...hreadid=490493

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)*(rang e1=criteria1)*(range1=criteria1),0))

array entered

--
Regards,

Peo Sjoblom

(No private emails please)


"duane" wrote in
message ...

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

Hi!

One way:

=INDEX(Z1:Z100,MATCH(1,(W1:W100="whatever")*(X1:X1 00="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" wrote in
message ...

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


--
Svenvlad
------------------------------------------------------------------------
Svenvlad's Profile: http://www.excelforum.com/member.php...o&userid=28916
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" wrote in
message ...

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