3

I have two sheets one: with two columns of places and a third column with the mileage between those places:

Sheet with Mileage; i.e., Location to Location

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.

Travel Expense Reimbursement Form; i.e., Travel Form

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?

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:

      Combined distance table and travel form

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.