Going CRAZY with INDEX and Match Function
 

First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which list an
Origin City and State abbreviations and a Destination City and State
abbreviations. The second tab is a Matrix which in the first column lists
state abbreviation and the top row lists state abbreviations as well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the state to
state abbreviations on my Origin worksheet and match it up on the Matrix
worksheet state to state abbreivation and return the value where the two
states meet.

I used the following formual which is working for some but not others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner

Going CRAZY with INDEX and Match Function
 

Sandi,

From what I understand I think you need to change the order of the two
MATCH.

=INDEX(Matrix!$C$5:$BL$65,MATCH(D380,Matrix!$C$3:$ BL$3,0),MATCH(B380,Matrix!$B$5:$B$65,0))

Does this work?
Kostis Vezerides

Sandi wrote:
First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which list an
Origin City and State abbreviations and a Destination City and State
abbreviations. The second tab is a Matrix which in the first column lists
state abbreviation and the top row lists state abbreviations as well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the state to
state abbreviations on my Origin worksheet and match it up on the Matrix
worksheet state to state abbreivation and return the value where the two
states meet.

I used the following formual which is working for some but not others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner

Going CRAZY with INDEX and Match Function
 

=INDEX(Matrix!$C$3:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))


"Sandi" wrote:

First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which list an
Origin City and State abbreviations and a Destination City and State
abbreviations. The second tab is a Matrix which in the first column lists
state abbreviation and the top row lists state abbreviations as well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the state to
state abbreviations on my Origin worksheet and match it up on the Matrix
worksheet state to state abbreivation and return the value where the two
states meet.

I used the following formual which is working for some but not others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner

Going CRAZY with INDEX and Match Function
 

Actually that looks up the destination first instead of the origin. I want
to look up the origin first and then the destination. Maybe I didn't
explain. here is how it's set up on the Origin sheet
A B C D
Origin City, State Destination City, ST

On the Matrix sheet it's as follows:

A B C D E F G H
AL CA FL GA IO KS LA MS
AL 125 130 140 150 125 115 110 125
CA 115 125 110 125 135 160 115 140
FL 175 125 135 180 165 110 200 145

So basically i need to formula to look at the Origin sheet starting with the
origin state and find it on the matrix sheet and then located the destination
on the Origin sheet and match it on the Matrix sheet
Example: if the Orgin/Destination was FL/GA then the formula would return
the value of 200 from the matrix sheet. Does that explain a little better?
--
Sandi Gardner


"vezerid" wrote:

Sandi,

From what I understand I think you need to change the order of the two
MATCH.

=INDEX(Matrix!$C$5:$BL$65,MATCH(D380,Matrix!$C$3:$ BL$3,0),MATCH(B380,Matrix!$B$5:$B$65,0))

Does this work?
Kostis Vezerides

Sandi wrote:
First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which list an
Origin City and State abbreviations and a Destination City and State
abbreviations. The second tab is a Matrix which in the first column lists
state abbreviation and the top row lists state abbreviations as well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the state to
state abbreviations on my Origin worksheet and match it up on the Matrix
worksheet state to state abbreivation and return the value where the two
states meet.

I used the following formual which is working for some but not others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner

Going CRAZY with INDEX and Match Function
 

that's didn't work - any other suggestions?
--
Sandi Gardner


"Teethless mama" wrote:

=INDEX(Matrix!$C$3:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))


"Sandi" wrote:

First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which list an
Origin City and State abbreviations and a Destination City and State
abbreviations. The second tab is a Matrix which in the first column lists
state abbreviation and the top row lists state abbreviations as well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the state to
state abbreviations on my Origin worksheet and match it up on the Matrix
worksheet state to state abbreivation and return the value where the two
states meet.

I used the following formual which is working for some but not others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner

Going CRAZY with INDEX and Match Function
 

Hi

You seem to have the dimensions wrong.
If the overall grid is C3:BL65 then the row required should be found
from B3:B65, not B5:B65

Try
=INDEX(Matrix!$C$3:$BL$65, MATCH(B380,Matrix!$B$3:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))


--
Regards

Roger Govier


"Sandi" wrote in message
...
that's didn't work - any other suggestions?
--
Sandi Gardner


"Teethless mama" wrote:

=INDEX(Matrix!$C$3:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))


"Sandi" wrote:

First off let me see if I can even explain this.
I have a workbook with 2 tabs. The first tab is the Origin which
list an
Origin City and State abbreviations and a Destination City and
State
abbreviations. The second tab is a Matrix which in the first column
lists
state abbreviation and the top row lists state abbreviations as
well and the
data of rates to charge.

What I am trying to do is create a formula that will look at the
state to
state abbreviations on my Origin worksheet and match it up on the
Matrix
worksheet state to state abbreivation and return the value where
the two
states meet.

I used the following formual which is working for some but not
others:
=INDEX(Matrix!$C$5:$BL$65, MATCH(B380,Matrix!$B$5:$B$65,0),
MATCH(D380,Matrix!$C$3:$BL$3,0))

I am beginning to be at a loss!!! Any help would be appreciated.
Sandi Gardner