Calling Excel Experts...

[loveindia]

Nenu oka tabular model chestunna... SQL Server lo between operator laaga Excel lo ela vaadali ani doubt... scenario is like below..

Sheet 1:
EmpID      EmpName        Date          DeptID(CalcualtedColumn)

1                 ABC                07/18/2016         (FORMULA)

Sheet 2:

EmpID      StartDate          EndDate         DeptID

1                01/01/2016      06/30/2016        1

2                07/01/2016       12/31/2016        2

So Sheet 1 lo unna EmpID and Date use cheskoni, sheet 2 lo unna DeptID ni pick cheskovaali... Lookup types lo cheyyali...  Normal ga Tabular lo RELATED ani oka keyword undi, I use it by creating a concatenated column and use that similar to a JOIN command in SQL .. (RELATED in DAX == JOIN in SQL)

So excel lo kind of VLOOKUP laantidi vaadi emanna idi cheyochaa... don't tell me just use MEDIAN or something... tried many things so far man... if you have anything working, please let me know...

SQL lo cheyochu gaa easy ani cheppakandi man... sql lo 1 min task, since I am doing Tabular services, I am keeping away as far as I can from the SQL System, just using it as source of truth for data thats it....

Thanks in advance... Lets do some discussion, if you have the answer....

Calling all Excel Experts, @tom bhayya  @Spartan @nissan @ronitreddy @4Vikram

 

[Staysafebro]

vlookup

[loveindia]

Just now, Staysafebro said:

vlookup

ushooooooo.... man painaa vlookup tho work aitey appudu cheppu anna kada man... tried it, didn't work for me man... let me know if you happened to do it man...

[Spartan]

no videa man..  calling $excel specialist @150bryant  @vikuba

[Staysafebro]

=VLOOKUP(A2,Sheet2!A2:D3,4,TRUE)

[loveindia]

Just now, Staysafebro said:

=VLOOKUP(A2,Sheet2!A2:D3,4,TRUE)

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....

[loveindia]

3 minutes ago, Spartan said:

no videa man..  calling $excel specialist @150bryant  @vikuba

Thanks for calling SaaraSooreedu man...

 

[tom bhayya]

excel VSD ki chaala dhuram ga untam man

[loveindia]

Just now, tom bhayya said:

excel VSD ki chaala dhuram ga untam man

only today come here man, from tomorrow no come man...

[Pehlwan]

21 minutes ago, Staysafebro said:

=VLOOKUP(A2,Sheet2!A2:D3,4,TRUE)

VLOOKUP(A2,Sheet1!$A$2:$D$<no of lines>,4,False)

this should work

replace content in sheet1 with sheet 2 and same for sheet2

 

I believe vlookup picks up only from left side...not sure...but i do it all the time for quarterly chargeback report 

[Batman_fan]

use Excel VBA.

Read each row from sheet1 and compare it with sheet2 each row using IF and write to different column.

[loveindia]

2 minutes ago, Pehlwan said:

VLOOKUP(A2,Sheet1!$A$2:$D$<no of lines>,4,False)

this should work

replace content in sheet1 with sheet 2 and same for sheet2

 

I believe vlookup picks up only from left side...not sure...but i do it all the time for quarterly chargeback report 

how will this vlookup perform the between of the two dates man? I understand you are doing the EmpID Match but what about the date?? It should fall between the startdate and enddate of the sheet2 man....

[loveindia]

3 minutes ago, Batman_fan said:

use Excel VBA.

Read each row from sheet1 and compare it with sheet2 each row using IF and write to different column.

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

[Pehlwan]

1 minute ago, loveindia said:

how will this vlookup perform the between of the two dates man? I understand you are doing the EmpID Match but what about the date?? It should fall between the startdate and enddate of the sheet2 man....

are u trying to get start and dates as well?

not sure I understand your question 

[loveindia]

Just now, Pehlwan said:

are u trying to get start and dates as well?

not sure I understand your question 

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...