1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Problems with formulas in Excel 2013

Discussion in 'Windows & Other Software' started by fish_dan, Apr 20, 2017 at 12:07 PM.

  1. fish_dan

    Gangster

    Joined: Aug 10, 2015

    Posts: 605

    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.
     
  2. DeathofRats

    Gangster

    Joined: May 22, 2011

    Posts: 229

    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.
     
  3. DeathofRats

    Gangster

    Joined: May 22, 2011

    Posts: 229

    Location: Smoggyville

  4. DeathofRats

    Gangster

    Joined: May 22, 2011

    Posts: 229

    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.

    [​IMG]
     
  5. fish_dan

    Gangster

    Joined: Aug 10, 2015

    Posts: 605

    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)

    [​IMG]

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

    Any ideas?
     
  6. Grrrrr

    Soldato

    Joined: Oct 23, 2002

    Posts: 5,654

    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)
     
  7. Lmg80

    Gangster

    Joined: Nov 20, 2016

    Posts: 117

    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
     
  8. fish_dan

    Gangster

    Joined: Aug 10, 2015

    Posts: 605

    Location: Derbyshire

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

    then the VLOOKUP will pull from these?
     


Share This Page