Gangster
Associate
Joined
10 Aug 2015
Posts
626
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.
 
Associate
Joined
22 May 2011
Posts
288
Location
Smoggyville
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.
 
Associate
Joined
22 May 2011
Posts
288
Location
Smoggyville
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.

 
Gangster
Associate
OP
Joined
10 Aug 2015
Posts
626
Location
Derbyshire
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?
 
Soldato
Joined
23 Oct 2002
Posts
5,719
Location
Various
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)
 
Associate
Joined
20 Nov 2016
Posts
764
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
 
Associate
Joined
22 May 2011
Posts
288
Location
Smoggyville
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?

Yup, you need a left bracket after match(1,
 
Associate
Joined
21 Jul 2004
Posts
280
Location
Dudley, West Mids
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.
 
Back
Top Bottom