VLOOKUP based on Id and date range

[kshastry]

I request you to kindly share any solution that you have for this simple looking problem. Thanks in advance.

I have a projects master table where I have following columns:
A: Employee Id
B: Start Date
C: End Date
D: Project Name

In another sheet I have following columns:
A: Audit Date
B: Emp Id
C: Project Name

Basically for a given Audit Date and Emp Id in sheet 2, I want to lookup and list everyone's project name on that date from sheet 1. Kindly help.

Note: I tried to look at solutions provided in this forum so far, but none exactly seemed to match my requirement.

[davesexcel]

You do not have an audit date in the first sheet.
Maybe supply a sample workbook.

[Down Under]

Ignoring audit date, assuming Emp ID is in cell B2 of sheet 2, then in cell c2 of sheet 2 put:

=Vlookup(b2,sheet1!$a$1:$d$200,4,false)

Replace d200 with whatever cell the last value in your range falls in. Sheet1 = names of your projects master table sheet.

Based on what you have given I don't see how an Audit date is relevant to your master table.

[kshastry]

Sorry for the confusion. Here is problem statement with more clarity:

I request you to kindly share any solution that you have for this simple looking problem. Thanks in advance.

I have a projects master table where I have following columns:
A: Employee Id
B: Start Date in the project
C: End Date in the project
D: Project Name

In another sheet I have following columns:
A: Audit Date
B: Emp Id
C: Project Name

Basically for a given Audit Date and Emp Id in sheet 2, I want to lookup and list everyone's project name on that date from sheet 1. So, I need to find row where audit date falls in between start/end date and emp Id should also match. Kindly help.

[kshastry]

Kindly check the sample excel file I uploaded. I am looking for a formula for desired result column.

[naveen4pundir]

Hi,

Pls check below attachment..

[kshastry]

Hi,

Pls check below attachment..

Naveen, thanks for your time. But the attached solution by you does not match desired result. Please let me know if I am missing something.

[Haseeb Avarakkan]

Hi kshastry,

in I2, with CTRL+SHIFT+NTER, rather than just ENTER

=LOOKUP(REPT("z",99),IF({1,0},"Data unavailable",INDEX(D$2:D$7,MATCH(1,IF(A$2:A$7=H2,IF(G2>=B$2:B$7,IF(G2<=C$2:C$7,1))),0))))

Then copy down.

[kshastry]

Hi kshastry,

in I2, with CTRL+SHIFT+NTER, rather than just ENTER

=LOOKUP(REPT("z",99),IF({1,0},"Data unavailable",INDEX(D$2:D$7,MATCH(1,IF(A$2:A$7=H2,IF(G2>=B$2:B$7,IF(G2<=C$2:C$7,1))),0))))

Then copy down.

Hi Haseeb, your solution worked for me. Thanks a ton. In addition, if you could kindly explain the first part, it will be great i.e. REPT("z",99),IF({1,0},

I am wondering about significance of "z" - why z? And when would I need to change 99 to a higher number?

Also, IF({1,0} also beats me as I am new to this level of formula writing.

Hope I am not pushing too much. If time permits, kindly share your knowledge. Else, I will still be thankful and will try to learn this elsewhere later :-)

[icestationzbra]

REPT("z",99) just repeats the letter 'z' 99 times and establishes a ridiculously large string of value for the LOOKUP function to match / reference / compare against.

as far as {1,0} is concerned, i am unclear about that - i think it means 1 = "Data unavailable", 0 = INDEX...

LOOKUP will find the farthest value lower than 'z' repeated 99 times. you would practically never need to increase that value of 99.

[kshastry]

Dear all, I have slightly more complicated problem statement now. Kindly find the attachment where in the comments section I have explained the problem statement in depth.

Kindly help as this is way beyond my expertise in excel. Thanks in advance.