Excel Formulas - using array formula across sheets

Question

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

Answer 1

Hi,

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

Answer 2

Ok this is what you match

A1=Sheet2!A1:A4445

B1=Shhet2!A1:A4445

the above are the match, formula look what is in A1 and B1 and match it with the row that contain the same combination in Sheet2!, when combination is found it pulls the information from the same row column C that is what Index(sheet2!C1:C4445 does,

 what I don't see in the formula you copy are the brackets {}

go to the cell where the formula is, click F2, now the formula is edited, press together CTRL+Shift holding both click on Enter

you can read more on how index match works here

http://www.contextures.com/xlFunctions03.html#IndexMatch

Answer 3

This is what my formula looks like....but doesn't have the open brackets.

=INDEX(Sheet2!C1:C4445,MATCH(1,(A1=Sheet2!A1:A4445)*(B1=Sheet2!B1:B4445),0))

There should only be one cell with one formula in Worksheet #1 but will be reading the information from Worksheet #2.

Answer 4

I've copied your formula below (with a slight change to the number of cells which would represent the number of scenarios (i.e. C1:C4445)

What I'm not getting is how this formula is recognizing the Place Name in Column A and the Place Name in Column B from Worksheet 1 with the Place Names from Worksheet #2. 

As my diagram from my previous message indicates, there is only one cell with a formula in Worksheet #1....that's all that is required.  However, the Place Names in both column A and column B can change.

=INDEX(Sheet2!C1:C4445,MATCH(1,(A1=Sheet2!A1:A4445)*(B1=Sheet2!B1:B4445),0))

Answer 5

Do you see the open brackets in the formula without editing it? { }

if yes, did you change Sheet 2 for your sheet name,

Did you change the range for yours C1:C6 , A1:A6 and B1:B6  all three have to have the same number of rows

formula without editing should looks like

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

post how your formula looks

here is how it looks like for me

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

another thing, check column A and B the names match exactly the ones in worksheet2

If the above doesn't work, [please delete any confidential information and upload a sample in onedrive posting the url to be able to look into it