can it be done without vb
 

I am trying to lookup two values and then find the corresponding value
from a matrix.

the list is very very long and has a column for code, a column for time
of day and a column of minutes.

Code TOD Mins
p2 Std 45678
p2 Eve 56430
p2 Wkd 32223

they are not always in the order of std eve wkd. And some instances
there may be just one time of day against a code,

so in the matrix is the time of day along the columns say b to d ;
Std Eve wkd

in the rows are the codes say p6 p2 p0

and then in the matrix are the rates

Std Eve Wkd
p6 3.5 2.5 1.5
p0 9.0 7.5 3.9
p2 4.8 3.0 2.1

i have tried to get to grips with offset and match but cannot get it to
work right.

any advice would be helpful.

Regards
Spikk


--
spikk
------------------------------------------------------------------------
spikk's Profile: http://www.excelforum.com/member.php...o&userid=24932
View this thread: http://www.excelforum.com/showthread...hreadid=384691

If the list for lookup is in Sheet1 and the table is in Sheet2, use
=INDEX(Sheet2!$B$2:$D$4,MATCH(Sheet1!A2,Sheet2!$A$ 2:$A$4,0),MATCH
(Sheet1!B2,Sheet2!$B$1:$D$1,0))
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

I am trying to lookup two values and then find the corresponding value
from a matrix.

the list is very very long and has a column for code, a column for time
of day and a column of minutes.

Code TOD Mins
p2 Std 45678
p2 Eve 56430
p2 Wkd 32223

they are not always in the order of std eve wkd. And some instances
there may be just one time of day against a code,

so in the matrix is the time of day along the columns say b to d ;
Std Eve wkd

in the rows are the codes say p6 p2 p0

and then in the matrix are the rates

Std Eve Wkd
p6 3.5 2.5 1.5
p0 9.0 7.5 3.9
p2 4.8 3.0 2.1

i have tried to get to grips with offset and match but cannot get it to
work right.

any advice would be helpful.

Regards
Spikk


--
spikk
------------------------------------------------------------------------
spikk's Profile: http://www.excelforum.com/member.php...o&userid=24932
View this thread: http://www.excelforum.com/showthread...hreadid=384691

Cheers for this, works a treat. I see where I was going wrong. My
thinking was that I had to address the whole matrix, not just the
values as is the case.


Many thanks, all very clear how this works. Easy when you know how.
:cool:

It was really bugging me but I was addressing the whole matrix, titles
and values.

Many thanks again i can now do the calcs i wanted, will save a lot of
time too.

Regards
Bob :)


--
spikk
------------------------------------------------------------------------
spikk's Profile: http://www.excelforum.com/member.php...o&userid=24932
View this thread: http://www.excelforum.com/showthread...hreadid=384691

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Cheers for this, works a treat. I see where I was going wrong. My
thinking was that I had to address the whole matrix, not just the
values as is the case.


Many thanks, all very clear how this works. Easy when you know how.
:cool:

It was really bugging me but I was addressing the whole matrix, titles
and values.

Many thanks again i can now do the calcs i wanted, will save a lot of
time too.

Regards
Bob :)


--
spikk
------------------------------------------------------------------------
spikk's Profile: http://www.excelforum.com/member.php...o&userid=24932
View this thread: http://www.excelforum.com/showthread...hreadid=384691