Calling Excel Experts...

[Batman_fan]

2 minutes ago, loveindia said:

I just want to see if I can do that as a formula because I plan to rewrite the same into DAX man....

not sure if we can achieve your request using any out-of-box excel function....I only know VBA

[Staysafebro]

27 minutes ago, loveindia said:

didn't understand your formula man... How do you know the data is always only upto A2:D3? have you tried something called OFFSET before? that can help with that find... but how will you apply the BETWEEN formula here man....

=VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!$A:$D,A2,4),4,TRUE)

Assuming dynamic rows

[Pehlwan]

3 minutes ago, loveindia said:

Sheet 1 lo EmpID and Date ni use cheskoni, Sheet 2 lo unna EmpID and BETWEEN StartDate AND EndDate between lo aa date (from sheet1) untey appudu it has to give the Dept ID...

pass uncle man

[Staysafebro]

i didn't see the date requirement but i am eating right now..will see what i can do

[Staysafebro]

=IF(Sheet2!B2<C2<Sheet2!C2,VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!$A:$D,A2,4),4,TRUE),"Out of range")

I hope I am getting to what you are asking for..

[Imperial]

57 minutes ago, loveindia said:

didn't understand your formula man... How do you know the data is always only upto A2:D3? have you tried something called OFFSET before? that can help with that find... but how will you apply the BETWEEN formula here man....

do A:A in that case, that looks up entire A 

[MRI]

very easy

[loveindia]

30 minutes ago, Staysafebro said:

=IF(Sheet2!B2<C2<Sheet2!C2,VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!$A:$D,A2,4),4,TRUE),"Out of range")

I hope I am getting to what you are asking for..

Use chesi chepta man..

[Staysafebro]

=IF(AND(Sheet2!C2>C2,C2>Sheet2!B2),VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!$A:$D,A2,4),4,TRUE),"Out of range")

try this too..make sure that the cells are set to date format... use format painter to adjust that

[Staysafebro]

Tested this one too

=IF(AND(Sheet2!C2>C2,C2>Sheet2!B2),VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!$A:$D,ROW(),4),4,TRUE),"Out of range")