Problems with formulas in Excel 2013

fish_dan

Gangster
Associate
Hello,

I am having some trouble trying to make my spreadsheet formulas work in an excel spreadsheet on dispatch times.
The idea was; i would have two sheets (Times (sheet1) & Data (sheet2))
Sheet 2 Data; would have lots of various destinations:
• Column A - Location 1
• Column B - Location 2
• Column C - Time taken from Loc 1 to 2
Sheet 1 Times; would be the locations I would receive on my day to day work:
E.G. a driver is going from Crawley to York.

So what I am trying to do is use a combination of VLOOKUPS and MATCH formulas; so when I input the locations into sheet 1, the formula will look up the data from sheet 2, find that location 1 & 2 match from sheet 2s data and then show the time taken data from sheet 2 into sheet 1 in column C

I am no expert at excel, but have been trying this for a while with little success.
Any help is very much appreciated.

P.S. I am unable to upload the spreadsheet because I do not know how.

DeathofRats

Associate
What you really need is a match index. It's a little difficult to explain without the real data, but if you google it I'm sure you will find the answer. The key is to use an array enter (CTRL SHFT ENTER), not just hit enter after typing a formula.

DeathofRats

Associate
knocked a quick example up, image uploaded. The trick really is to remember to do the ctrl shift enter (which will add the curly brackets). Probably a dozen ways of doing the same trick - the microsoft link uses if statements, both methods work fine.

fish_dan

Gangster
Associate
OP
Hi Death,

Thank you very much for the help! This is defiantly pointing me in the right direction.
So I have made a mock up SS and entered the following:

=Index(Sheet2!\$A\$2:\$C\$2,Match(1,Sheet2!\$A\$2:\$A\$7=Sheet1!A2)*(Sheet2!\$B\$2:\$B\$7=Sheet1!B2),0),3)

The error highlights ",3)" when I press Ctrl Shift and enter on the formula.

Any ideas?

Grrrrr

Soldato
I'd suggest avoiding array formulas if you're not already familiar with them. They're difficult to understand and can slow down large workbooks.

The simplest solution would be:
• Add a new column to the left of sheet 2 called something like "Lookup Column"
• Populate that column so that it reads something like "Location X to Location Y" e.g. =B2 & " to " & C2.
• Now in sheet 1 you can just use a simple VLOOKUP to get the time. e.g. =VLOOKUP(A1&" to "&B1,Sheet2!\$A:\$D,4,FALSE)

Lmg80

Associate
I'd suggest avoiding array formulas if you're not already familiar with them. They're difficult to understand and can slow down large workbooks.

The simplest solution would be:
• Add a new column to the left of sheet 2 called something like "Lookup Column"
• Populate that column so that it reads something like "Location X to Location Y" e.g. =B2 & " to " & C2.
• Now in sheet 1 you can just use a simple VLOOKUP to get the time. e.g. =VLOOKUP(A1&" to "&B1,Sheet2!\$A:\$D,4,FALSE)

I agree a concatenation string in column A and a simpler lookup is what I would be looking at doing unless the range on Sheet1 is constantly updated ergo lookup would need redoing periodically, then the dynamic approach with index/match makes sense.

However if it was me, I like to over engineer things so would prob have a form / macro affair

fish_dan

Gangster
Associate
OP
[LIST said:
[*]Populate that column so that it reads something like "Location X to Location Y" e.g. =B2 & " to " & C2.
[/LIST]

As in re-name the columns B2 & the & C2?

then the VLOOKUP will pull from these?

DeathofRats

Associate
Hi Death,

Thank you very much for the help! This is defiantly pointing me in the right direction.
So I have made a mock up SS and entered the following:

=Index(Sheet2!\$A\$2:\$C\$2,Match(1,Sheet2!\$A\$2:\$A\$7=Sheet1!A2)*(Sheet2!\$B\$2:\$B\$7=Sheet1!B2),0),3)

The error highlights ",3)" when I press Ctrl Shift and enter on the formula.

Any ideas?

Yup, you need a left bracket after match(1,

fish_dan

Gangster
Associate
OP
Yup, you need a left bracket after match(1,

When i add the bracket the whole formula lights up indicating an error...

parko

Associate
You've added the bracket before the 1 not after.

=Index(Sheet2!\$A\$2:\$C\$2,Match(1,(Sheet2!\$A\$2:\$A\$7=Sheet1!A2)*(Sheet2!\$B\$2:\$B\$7=Sheet1!B2),0),3)

Once you've amended the formula you need to press ctrl, shift and enter not just enter. This adds the {} brackets in.