I have two sheets one: with two columns of places and a third column with the mileage between those places:
On the other sheet I have a drop down list of the places in Sheet1 column A
and another column with a drop down list of the places in Sheet1 column B
, then a third column that match index looks up the mileage from Sheet 1 column C
.
In my index match formula, I want to be able to look up the places no matter which column they are in on the first sheet.
For example, I want it to find a match for Office to Apple Blossom and Apple Blossom to Office.
Now I am using the formula:
=ArrayFormula(iferror(ArrayFormula(INDEX('Copy of Location to Location'!C:C, MATCH('Travel Form 17-18'!B8&'Travel Form 17-18'!C8,'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B,0))),""))
but this only looks up Office to Apple Blossom and not the reverse. Is it possible to add another Match formula that would look up both columns without having to enter the data into both columns on Sheet 1?