Thread: 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

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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.

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

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>

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 <spikk.1rtfij_1120766842.1879@excelforum-nospam.com>,
spikk.1rtfij_1120766842.1879@excelforum-nospam.com 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.
>
>
> 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
>
>

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 <spikk.1rpvqk_1120601173.2143@excelforum-nospam.com>,
spikk.1rpvqk_1120601173.2143@excelforum-nospam.com 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
>
>