Autofill if both text criteria are met

[fawn728]

I want to use a formula/function that states if address one is in one column and address two is in the same row in the next column then column 3 should be this number. This is a travel log and I want it to autofill for several offices. So if Washington County and Clinton County are next to eachother the next column should say 26. I need to do that for appx. 12 combination of addresses.

 

[dreid1011]

Separated like this?

Yes. Is there only 1 address per county, no duplicates? If so, you could just do the county names. That would make the lookup easier.

 

[dreid1011]

Assuming your mileage table is on a different sheet, how about this:

Sheet1 = sheet with mileage you want to autofill from mileage table

Book1
	A	B	C	D
1	Commute Miles (Y or N)	Departed From (City,State)	Arrived At (City,State)	Mileage
2		Washington County	Clinton County	26
3		Washington County	Washington County
4		Washington County	Jefferson County	29
5		Jefferson County	Clinton County
6		Clinton County	St Rose Dev Club
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=XLOOKUP(B2&C2,Sheet2!$A$2:$A$8&Sheet2!$B$2:$B$8,Sheet2!$C$2:$C$8,"")

Sheet2 = sheet with mileage table combinations

Book1
	A	B	C
1	Depart	Arrive	Mileage
2	Washington County	Marion County	43
3	Washington County	Clinton County	26
4	Clinton County	Bond County	16
5	Washington County	Bond County	42
6	Washington County	Jefferson County	29
7	Clinton County	Jefferson County	50
8	Clinton County	Marion County	32
Sheet2