# Solved Excel 2016 cell calculation based on 2 different lists

Discussion in 'Business Applications' started by Ripperdan, Oct 1, 2017.

Not open for further replies.

Joined:
Oct 28, 2009
Messages:
61
First Name:
Danny
In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1, 64 bit
Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 12227 Mb
Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
Antivirus: Webroot SecureAnywhere, Enabled and Updated

File size:
17.5 KB
Views:
1
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
try
http://skp.mvps.org/xl00002.htm

as entered
=IFERROR(INDEX('DATA Sheet'!\$B\$2:\$K\$11,MATCH('Mileage Log '!B8,'DATA Sheet'!\$A\$2:\$A\$11,0),MATCH('Mileage Log '!C8,'DATA Sheet'!\$B\$1:\$K\$1,0)),"")

#### Attached Files:

• ###### Mileage Log Template etaf.xlsx
File size:
18.3 KB
Views:
2

Joined:
Oct 28, 2009
Messages:
61
First Name:
Danny
Works perfect. Thank you very much.

I hope I will be able to see how to modify it if and when the DATA Sheet needs to be modified.

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
the link explains how it works
so you should be ok
if not , come back here

As Seen On