CODEWITHSUNDEEP
excelformula
Lost
Lost

Reputation: 11

IF formula too long

I’m trying to create a formula that will display mileage from one place to another.

Example: column one is location combinations (there are 39 locations and multiple combinations) Eg-sams to Petes, sams to mc d, mc d to sams etc.

Last column with formula would automatically place mileage from point a to point b. Etc

The formula i created was IF but way too long

=IF(B12="SVES TO KHS",11, 
    IF(B12="SVES TO FRHS",4.1,
    IF(B12="SVES TO CHS",6.9,
    IF(B12="SVES TO KMS",9.5,
    IF(B12="SVES TO ISM",6.2,
    IF(B12="SVES TO HM",5.3,
    IF(B12="SVES TO FHM",2.4,
    IF(B12="SVES TO TSM",7.6,...

Is there a way to shorten the formula?

Upvotes: 0

Views: 734

Answers (2)

user4039065
user4039065

Reputation:

An X-to-Y/Y-to-X distance matrix should be your best bet. While VLOOKUP is good for a one-column-lookup/one-column-retrieval, an INDEX/MATCH/MATCH would be more appropriate for a true matrix.

Assume the folowwing data matrix with destinations along the first row and the first column.

      a       b       c       d       e       f       g
a     -      40      80      17      37      16      70 
b    40       -      48      95      85       8      60 
c    80      48       -      24      26      75      73 
d    17      95      24       -      14       9      56 
e    37      85      26      14       -      91       7 
f    16       8      75       9      91       -      78 
g    70      60      73      56       7      78       -

Note that distances like c-to-f are the same as f-to-c. (yes, there i a simple formula for this but that is another question). Obviously, any x-to-x or y-to-y should be zero when x = y.

In the sample image below your formula in L2 should be,

=INDEX($B$2:$H$8, MATCH(J2, A$2:A$8, 0), MATCH(K2, B$1:H$1, 0))

enter image description here

The cell and row highlighting were added with a couple simple conditional formats formulas.

Upvotes: 0

David wyatt
David wyatt

Reputation: 318

Best thing to do is create a separate table on sheet 2, in column a have a list of answers "SVES TO ", column b the miles. Then use a vlookup to find the miles

=Vlookup (b12, sheet 2!'a1:b50,2,0)

In this example there are 50 different SVES TO examples, change it to however many you have.

Upvotes: 1

Related Questions