Share via

Excel Formulas - using array formula across sheets

Anonymous
2016-06-06T14:23:41+00:00

I'm looking for assistance in creating a formula for the following:

  • In Worksheet 1, Cell A1 has a dropdown menu of place names where a person will travel from (i.e. Place A).  Cell B1 has a dropdown menu of place names where a person will travel to (i.e. Place B)
  • I want to create the formula in Cell C1 that will read from Worksheet 2 in the same document.
  • Worksheet 2 contains Column A, with a list of place names to travel from (i.e. Place A, Place B, Place C, etc.....).  In Column B of this same Worksheet I have a list of place names to travel to (i.e. Place A, Place B, Place C, etc....).  Column C contains the number of kilometers from one place to the next (see below).

How can I get Worksheet 1 to read from Worksheet 2 based on the place names selected from the dropdown menus in Worksheet 1?

Any help you can offer would be appreciated.

Place A Place B 177
Place B Place C 271
Place C Place D 146
Place D Place E 275
Place E Place F 372
Place F Place G 189
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2016-06-07T00:05:58+00:00

Hi,

It looks like the numeric columns has some invisible entries in some/all cells.  Remove those entries.

Was this answer helpful?

0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-06T17:41:07+00:00

    Thank you again!!  I'm not sure what it is that I'm doing incorrectly but I can't seem to get that formula to work for me. 

    Any suggestions on what I could try next??

    Thanks!

    :)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-06-06T17:21:10+00:00

    Yes this is what the formula does, sheet2 represents your worksheet 2, index is pulling the information from sheet 2 column C, then match is comparing A1 and B1 with sheet2 A1 and Sheet 2 B1, I tested and got the results you want, don't; forget to enter the formula as explained is an array formula, when you enter you will see this

    {Formula}

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-06-06T16:38:22+00:00

    Thank you for your reply.  However, the formula you suggested did not work.  Maybe I didn't explain myself well.....hoping the diagram below will further help explain what it is that I require from the formula.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-06-06T15:38:12+00:00

    Hi, this is an array formula have to be entered pressing CTRL+Shift+Enter

    =INDEX(Sheet2!C1:C6,MATCH(1,(A1=Sheet2!A1:A6)*(B1=Sheet2!B1:B6),0))

    Was this answer helpful?

    0 comments