Intersect Formula???

[scrabtree]

Lets say in sheet 1 the user will type a "From Location"
(i.e. New York) into Cell A1, and a "To Location" (i.e.
Boston) into Cell B1. I want cell C1 to equal the milage.

In sheet 2 there is a distance chart with from location
arranged in Cells A1:Z1. The To Locations are in cells
A2:A25. The row/column intersect contains the miles.

How can I get cell C1 in sheet one to equal the
corresponding intersect value based on what they typed in
Sheet 1 A1 and B1?

SDC

 

[Tom Ogilvy]

You have created a conflicting situation where you placed your inputs and
results in the from locations.

Let's say that the from is in A1, the to is in B1

The from locations are in A2:Z2
the to locationsin A3:A25

=OFFSET($A$2,MATCH(B1,$A$2:$A$25,0)-1,MATCH(A1,$A$2:$Z$2,0)-1)

 

[Bernard V Liengme]

In A2 use =MATCH(A1,Sheet2!A1:Z1,0) this tells the column position of the
From City
In B2 use =MATCH(B1,Sheet2!A2:A25,0) this tells the row position of the To
City
In C1 use =INDEX(B2:Z25, B2,A2) this locates the intersection (as
you call it)
In C2 let's combine the formulas
=INDEX(B2:Z25,MATCH(B1,Sheet2!A2:A25,0),MATCH(A1,Sheet2!A1:Z1,0))

Best wishes
Bernard Liengme
(e-mail address removed)

 

[Tom Ogilvy]

Missed you were on different sheets, but you should be able to adjust the
formula.