Excel 2013 Formula Help

[fish_dan]

Hi Everyone,

I'm having trouble with getting a formula to work on Excel 2013.
I have a list or raw data on Sheet2 it contains 3 columns:

• Location 1
• Location 2
• Time taken

What I am trying to achieve is; in Sheet1, when I enter in two of the locations that match from Locations 1&2 (in Sheet2), the 'Time taken' figure will be displayed (in Sheet1).

I have messed around with INDEX/MATCH & VLOOKUP but nothing is working... I must be going about it the wrong way?

Any ideas?

Thanks

Dan

 

[AHarvey]

can you host it somewhere so I can download it and play around with it?

i'm guessing it's going to be nested IF statements

 

[Armageus]

Something like this would work:
https://exceljet.net/formula/vlookup-with-two-or-more-criteria

e.g. on sheet2, add an additional column in front that concatenates the two Locations e.g. to give StokeLondon or DerbyNewcastle e.g. in cell A2 you would have =B2&C2

Your Vlookup on Sheet 1 can then be something like:
=VLOOKUP(A2&B2,Sheet2!A2:Sheet2!D4,4)

 

[fish_dan]

can you host it somewhere so I can download it and play around with it?

i'm guessing it's going to be nested IF statements

Hi Harvey,

Any idea how I would go about sharing/hosting this?
I could email you the document?

 

[fish_dan]

Bump.

 

[WillyNelson]

You need a SUMPRODUCT

Where column A is Location 1, B is location 2 and C is time taken. Say you have 6 rows of data from A2 to C7.
L1 is your first location, L2 is your second.

=SUMPRODUCT((A2:A7=L1)*(B2:B7=L2)*(C2:C7))

 

[fish_dan]

Do you know how i can put my SS on here so you can see what i mean?

 

[BigT]

Put it on some cloud storage that allows you to publish a public link and put the URL in this post. Dropbox will do it for example

 

[fish_dan]

Put it on some cloud storage that allows you to publish a public link and put the URL in this post. Dropbox will do it for example

OK I have put it onto Dropbox, how do i link it on here?

 

[BigT]

https://www.dropbox.com/en/help/167

 

[fish_dan]

Please could you PM me your email address so I can add you to my Dropbox sharefolder, so you can see the document?

 

[BigT]

You'll need to setup your Trust on here I think for me to be able to do that.

 

[fish_dan]

So after more playing with this formula i know that i will need VLookup 100%:

=IFERROR(VLOOKUP(""&A82,Location!A:C,2,FALSE)," ")

But i also need Match in there somewhere to make it work... However when i add it; i always get #DIV error

Any ideas anyone?