Information returned by location and time range

  • As shown in the two tables below, I need to return information based upon both Location and Time. I can use the MATCH function in an array formula, but the criteria (including time) must match exactly. That won't do. A vlookup (TRUE) handles inexact times, but I can't think how to add the second criteria. I tried a couple times using a cheater column that concatenates Location and Time in different forms, but couldn't get that to work. Perhaps I could use match to find the row of the Location and then use indirect and VLOOKUP to handle the time within the range found by the match, but that seems really convoluted. I have the sneaking suspicion that I'm just having a brain cramp and this should be easy, but for the life of me I'm not figuring it out.


    Any help will be greatly appreciated.


    Thanks in advance,
    Andrew


    Update 1: Fixed typo with Data Table times.
    Update 2: It looks like I can do it using MATCH to find the Location range and then VLOOKUP (TRUE) to find the Information by the Time (see attachment). But it still seems to me like there should be something easier.


    Results Table [TABLE="align: left, border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    Location

    [/td]


    [td]

    Time

    [/td]


    [td]

    Information (from Data Table)

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    06:05:32

    [/td]


    [td]

    Needs to return "Info A"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    07:10:15

    [/td]


    [td]

    Needs to return "Info B"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    09:05:05

    [/td]


    [td]

    Needs to return "Info C"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    15:15:15

    [/td]


    [td]

    Needs to return "Info A"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    06:05:32

    [/td]


    [td]

    Needs to return "Info E"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    15:15:15

    [/td]


    [td]

    Needs to return "Info F"

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    16:00:00

    [/td]


    [td]

    Needs to return "Info G"

    [/td]


    [/tr]


    [/TABLE]














    Data Table [TABLE="align: left, border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    Location

    [/td]


    [td]

    Time

    [/td]


    [td]

    Information

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    00:00:00

    [/td]


    [td]

    Info A

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    07:00:00

    [/td]


    [td]

    Info B

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    08:00:00

    [/td]


    [td]

    Info C

    [/td]


    [/tr]


    [tr]


    [td]

    Location 1

    [/td]


    [td]

    00:00:00

    [/td]


    [td]

    Info A (Perhaps this row isn't needed.)

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    00:00:00

    [/td]


    [td]

    Info E

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    15:00:00

    [/td]


    [td]

    Info F

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    16:00:00

    [/td]


    [td]

    Info G

    [/td]


    [/tr]


    [tr]


    [td]

    Location 2

    [/td]


    [td]

    00:00:00

    [/td]


    [td]

    Info E(Perhaps this row isn't needed.)

    [/td]


    [/tr]


    [tr]


    [td]

    Location 3

    [/td]


    [td]

    00:00:00

    [/td]


    [td]

    Info I

    [/td]


    [/tr]


    [/TABLE]

    Files

    [SIZE="1"][COLOR="darkred"][FONT="Arial"]There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge[/FONT][/COLOR][/SIZE]

  • Maybe this formula in L8, you must use control + shift + enter to confirm the formula as it is an array formula and then you can copy it normally to the other cells.


    =INDEX($L$17:$L$25,MATCH($J8&$K8,$J$17:$J$25&$K$17:$K$25,1))

    Bruce :cool:

  • Perfect Skywriter. It didn't even cross my mind to use INDEX. Thank you very much.

    [SIZE="1"][COLOR="darkred"][FONT="Arial"]There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge[/FONT][/COLOR][/SIZE]