Fill cell from table based on others cells contents

[Jaypee666]

Hi all.

I know I've a workbook someplace with what I'm trying to achieve here already in it, but for the live of me I can't find it.

Anyways, here goes, and I know it'll be something simple.

I have a workbook I fill in daily specifying date (Col A) start location (Col B) and end location (Col C) along with the distance (Col D) between the 2.
I have a 2nd sheet called Distances, which already has a list of these trips and their lengths, with start location (Col A) and end location (Col B) along with the distance (Col C)

What I am trying to is when I input Start & End locations that Distance will be automatically filled in based on these 2 cells.

All help greatly appreciated.

 

[onlyadrafter]

Hello,

a formula you could use is:

=INDEX(Sheet1!C:C,MATCH(B1&C1,Sheet1!$A$1:$A$10&Sheet1!$B$1:$B$10,0),0)

you will need to change workbook name and cell ranges as required. You will also need to use CTRL + SHIFT + ENTER (array formula) when entering the formula, not just enter.

Not sure what you mean by automatically filled in.

Does this give required results?

 

[Peter_SSs]

If the location combinations in columns A & B of the Distances sheet are unique, then you could also try

=SUMIFS(Distances!C$2:C$100,Distances!A$2:A$100,B2,Distances!B$2:B$100,C2)

 

[Jaypee666]

@Peter_SSs That works perfectly as does yours @onlyadrafter

Thank you both.

I'm going with Peters for the moment, but can ye advise as to the difference between the 2 methods please?

 

[Peter_SSs]

Thank you both.

You're welcome. Glad we could help.

difference between the 2 methods

On 'Distances', mine adds all the values in column C where column A matches the start location and column B matches the end location. This only works if that combination of locations only appears once on 'Distances'

On 'Distances' onlyadrafter's formula joins all the column A and column B values together then looks down that list until it finds a match with the start & end locations joined together and retrieves the value from column C at that point.

There is a slight possibility of an error with that formula if your locations were say codes as shown in my example below.

Jaypee666 1.xlsm
	A	B	C
1	Start	End	Dist
2	ab	cdef	50
3	abc	def	200
Distances

You will see that in E2 below there is an incorrect result & this has stemmed from the direct joining of values. However, the slight modification of that formula, adding a delimiter, in F2 resolves that potential issue.
IF you have the FILTER function in your Excel 365, another option is shown in G2

Jaypee666 1.xlsm
	B	C	D	E	F	G
1	Start	End	Dist	Dist	Dist	Dist
2	abc	def	200	50	200	200
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMIFS(Distances!C$2:C$10,Distances!A$2:A$10,B2,Distances!B$2:B$10,C2)
E2	E2	=INDEX(Distances!C:C,MATCH(B2&C2,Distances!$A$1:$A$10&Distances!$B$1:$B$10,0),0)
F2	F2	=INDEX(Distances!C:C,MATCH(B2&"|"&C2,Distances!$A$1:$A$10&"|"&Distances!$B$1:$B$10,0),0)
G2	G2	=INDEX(FILTER(Distances!C$2:C$10,(Distances!A$2:A$10=B2)*(Distances!B$2:B$10=C2),""),1)

 

[Jaypee666]

@Peter_SSs good information to have for the future.

Thank you for the explanation and examples

 

[Peter_SSs]

Cheers.