Vlookup result only when an id matches AND dates are within a range

[unreal_event_horizon]

Hi All,
been a while!!!

i know i should be posting a book but this data is sensitive and i'd find it hard to create a demo version so i'll do my best to explain.


Column A on book one contains and identifier, Column C on the results book has the same IDENTIFIER,

then each identifier on book one has an in date and an out date

on the results sheet there is a service date,

i am trying to do is, vlookup up a result but only when the ID numbers match and the service date is between in date and out date.

my attempt.

=AND(VLOOKUP(C8,[Book1]Sheet1!$A:$H,8,0),B8>=[Book1]Sheet1!$C:$C,B8<=[Book1]Sheet1!$D:$D)

any questions blast away!

cheers
D

[Jonmo1]

Are the dates in Book one Sorted in any way?
Are those date ranges all inclusive? i.e. say D2 is 7/31, then C3 is 8/1 (no gaps between end dates in D and the next begin dates in C)

[avk]

If you attach sample file, then clear idea. (mentioned expected result in required cell)

[unreal_event_horizon]

Hi Jonmo,

thanks for taking the time to reply,

a caveat i failed to mention, the ID is unique, but it may appear several times with different dates, a different result may
be next to each date and ID, i.e.

ID Dates Results
1234 01/01/16 - 3/01/16 Result:engine overhaul
2356 3/01/16 3/01/16 Result: Tire Change

my new attempt, but i fear the vlookup isnt throwing back results based on the ID and its Active date

=IF(AND(VLOOKUP(D2,[Book1.xlsx]Sheet1!$A:$A,1,FALSE),LOOKUP(B2,[Book1.xlsx]Sheet1!$C$2:$D$2147)),VLOOKUP(D2,[Book1.xlsx]Sheet1!$A:$H,8,FALSE))

[Special-K]

Does this work? You may need to open the Book1 workbook for this to work.

=VLOOKUP(C8,IF(AND(B8>=[Book1]Sheet1!$C1:$C100000,B8<=[Book1]Sheet1!$D1:$D100000),[Book1]Sheet1!$A1:$H100000),8,0)
Array formula, use Ctrl-Shift-Enter

[Jonmo1]

You didn't really answer my questions.

Also, what type of data is in column H that the vlookup should be returing?
Is it TEXT or Number ?

[unreal_event_horizon]

my apologies to all for being unclear, and for not answering questions, due to the sensitive nature of the data, i have made up a dummy file.

in sheet account data, you will see c2 is an expected result.

there is a second sheet called service bay data, this is the data i need to compare my data against.

in english, vlookup a Customer number when the Service date on the account data sheet is between "arrived in bay" and "left bay dates"
on the service bay sheet, then return what service they got.

i hope that makes sense, and apologies for wasting anyones time!

(uploading file please wait)

[unreal_event_horizon]

I'm clearly an idiot and cant see where to upload files anymore?
any help lol

[AliGW]

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[unreal_event_horizon]

***********************see below for attachment

[Special-K]

Im getting invalid attachment.

[unreal_event_horizon]

Hopefully this attachment has worked, I had to remake the excel file, had a corruption issue yesterday.


and a side note to all , thanks for your patience

[Jonmo1]

Try

=INDEX('Service Bay Data'!$E$2:$E$8,MATCH(1,INDEX(('Service Bay Data'!$B$2:$B$8=A2)*('Service Bay Data'!$C$2:$C$8<=B2)*('Service Bay Data'!$D$2:$D$8>=B2),0),0))

[unreal_event_horizon]

incredible, exactly what i was after Jonmo1! cheers for the patience!

i broke it down to use it else where and have learned from it!

thanks to all! marking Solved

[AliGW]

To mark the thread as solved, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Jonmo1]

You're welcome.