# Index Match across multiple columns

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?

First rule of rocket science: simple things are easier than complex things.

I recreated your problem on a single sheet, so I wouldn’t have to use sheet names everywhere.  Columns `A`-`C` correspond to Columns `A`-`C` on your Sheet1 (a.k.a. `'Copy of Location to Location'`) and Columns `AA`-`AE` correspond to Columns `A`-`E` on your Sheet2 (a.k.a. `'Travel Form 17-18'`).  I reduced your formula (which you use in `Sheet2!E8`) to

``````=IFERROR(INDEX(C:C, MATCH(AB8&AC8, A:A&B:B, 0)), "")
``````

which I put into my `AE8`.  It’s a lot easier to understand when the clutter is stripped away.

And the logic is not rocket science.  If `FROM&TO` isn't in the “Location to Location” table, we want to search for `TO&FROM`:

``````=IFERROR(INDEX(C:C, IFERROR(MATCH(AB8&AC8, A:A&B:B, 0), MATCH(AC8&AB8, A:A&B:B, 0))), "")
``````

is the formula I have in cell `AE8` in this screenshot:

We’re apparently using different versions of Excel.  I can’t say `ArrayFormula(…)` in mine (Excel 2013); I just type Ctrl+Shift+Enter after a formula to make it an array formula.  So I don’t know exactly how this `ArrayFormula(…)` works (are you sure you need to use it twice in your formula?).  But here’s my solution (from above) translated back into your sheet and column names:

`=IFERROR(INDEX(C:C, IFERROR(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), MATCH('Travel Form 17-18'!C8&'Travel Form 17-18'!B8, 'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B, 0))), "")`

I’ll let you figure out where in there you need to say `ArrayFormula(…)`.

• Thanks so much! The formula was in google sheets so that's why it showed "ArrayFormula" at the beginning. – Lauren McCoy Sep 10 '17 at 17:22
• You’re welcome.  Since you’re new here, I suggest that you take our Tour, and then glance at What should I do when someone answers my question? (a one-page-long article in our Help Center).  It explains that, when an answer solves your problem, you should “accept” it by clicking the checkmark to the left of the first paragraph.  Doing so tells future readers that this answer works, and is our community’s way of saying “Thanks” (although brief comments, like the one you made [above], are generally tolerated). – Scott Sep 10 '17 at 20:52