Problems with formulas in Excel 2013

fish_dan

fish_dan

fish_dan

Gangster
Associate
Joined
10 Aug 2015
Posts
634
Location
Derbyshire
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.
Any further questions, please ask.

P.S. I am unable to upload the spreadsheet because I do not know how.
 
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.
 
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.

 
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)

BPWjW.png

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

Any ideas?
 
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)
 
Grrrrr said:
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)
Click to expand...

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 said:
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)

BPWjW.png


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

Any ideas?
Click to expand...

Yup, you need a left bracket after match(1,
 
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.
 
You must log in or register to reply here.
Back
Top Bottom