Okay I have been tasked with the creation of a mileage sheet that will calculate our employees mileage with little effort from the employees.
We have 13 different locations that we drive to frequently. I created a table that contains the distance from each location to any other given location. This table is named array1 in the spreadsheet. I then created two columns in which the employee inputs his starting location and his departing location. There is a final column that contains a formula that searches array1 based on the locations the employee entered in the previous to cells.
The way this formula works is with a vlookup formula nested in vlookup formula. The exact formula is pasted below:
VLOOKUP(C12,array1,VLOOKUP(D12,count,2,FALSE))
So basically it looks at the value in C12 (starting location), then searches for it array1. When it finds it, it does a vlookup for d12 (ending location) in an array named count.
Count is an array that contains all our locations, and their index in array1.
For example in array1, CO is second entry in the array. So in the count array, CO's value is 2.
So the second vlookup will return a value of 2, which will tell the first vlookup to search in the second column and return that data.
This method works wonderfully except for two locations. It returns an #NA when the BF & BR locations are used as the starting destinations. If they are the ending locations it works fine.
This has led me to believe that there is an inconsistency somewhere in my sheet regarding text or number format. But I cannot find one to save my life. I hope this makes sense and it is an easy fix, but I have looked at this thing until I am blue in the face... like this guy
I know the details might be somewhat confusing, so I included a link to my sheet so you can get a look at it in action. Any help is hugely appreciated. Thank you.
http://www.bwoodlee.com/mileage.xlsx
We have 13 different locations that we drive to frequently. I created a table that contains the distance from each location to any other given location. This table is named array1 in the spreadsheet. I then created two columns in which the employee inputs his starting location and his departing location. There is a final column that contains a formula that searches array1 based on the locations the employee entered in the previous to cells.
The way this formula works is with a vlookup formula nested in vlookup formula. The exact formula is pasted below:
VLOOKUP(C12,array1,VLOOKUP(D12,count,2,FALSE))
So basically it looks at the value in C12 (starting location), then searches for it array1. When it finds it, it does a vlookup for d12 (ending location) in an array named count.
Count is an array that contains all our locations, and their index in array1.
For example in array1, CO is second entry in the array. So in the count array, CO's value is 2.
So the second vlookup will return a value of 2, which will tell the first vlookup to search in the second column and return that data.
This method works wonderfully except for two locations. It returns an #NA when the BF & BR locations are used as the starting destinations. If they are the ending locations it works fine.
This has led me to believe that there is an inconsistency somewhere in my sheet regarding text or number format. But I cannot find one to save my life. I hope this makes sense and it is an easy fix, but I have looked at this thing until I am blue in the face... like this guy
I know the details might be somewhat confusing, so I included a link to my sheet so you can get a look at it in action. Any help is hugely appreciated. Thank you.
http://www.bwoodlee.com/mileage.xlsx
