can it be done without vb - Page 2

#16 09-06-2005, 03:05 AM

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

#17 09-06-2005, 04:05 AM

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

#18 09-06-2005, 04:05 AM

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

#19 09-06-2005, 06:05 AM

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

#20 09-06-2005, 06:05 AM

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

#21 09-06-2005, 07:05 AM

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

#22 09-06-2005, 07:05 AM

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

#23 09-06-2005, 10:05 AM

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

#24 09-06-2005, 10:05 AM

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

#25 09-06-2005, 12:05 PM

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

#26 09-06-2005, 12:05 PM

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

Welcome to the Excel Forum

Forum Statistics