Home

Lookup Value from two sheets by dates

%3CLINGO-SUB%20id%3D%22lingo-sub-263786%22%20slang%3D%22en-US%22%3ELookup%20Value%20from%20two%20sheets%20by%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20trying%20to%20lookup%20ID%20values%20from%20two%20sheets%20which%20have%20dates%20..I%20am%20trying%20to%20lookup%20value%20by%20date....Can%20you%20please%20help%20me%20%2C%2CI%20have%20attached%20sample%20excel%20sheet%20and%20highlighted%20the%20sample%20below%20too....Under%20Sheet%201%20I%20am%20expecting%20the%20answer%20as%20in%20lookup%20value-Answer%20expected%20column%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet1%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3EDate%20ID%20Lookup%20Value-Answer%20expected%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E1-Sep%20103%20103%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E1-Sep%20102%20102%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E1-Sep%20101%20NA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E4-Sep%20105%20105%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E4-Sep%20106%20106%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20101%20NA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20104%20NA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20106%20NA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20199%20199%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20200%20200%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESheet%202%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDate%20ID%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E1-Sep%20102%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E1-Sep%20103%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E4-Sep%20105%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E4-Sep%20106%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20199%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E5-Sep%20200%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-263786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263835%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Value%20from%20two%20sheets%20by%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263835%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIFS(Sheet2!%24A%242%3A%24A%247%2CA2%2CSheet2!%24B%242%3A%24B%247%2CB2)*B2%3C%2FPRE%3E%3CP%3Eor%3A%3C%2FP%3E%3CPRE%3E%3DSUMIFS(Sheet2!%24B%242%3A%24B%247%2CSheet2!%24A%242%3A%24A%247%2CA2%2CSheet2!%24B%242%3A%24B%247%2CB2)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679492%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Value%20from%20two%20sheets%20by%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3AThanks%20!..%3C%2FP%3E%3C%2FLINGO-BODY%3E
Arun Chandramouli
New Contributor

Hi All,

I am trying to lookup ID values from two sheets which have dates ..I am trying to lookup value by date....Can you please help me ,,I have attached sample excel sheet and highlighted the sample below too....Under Sheet 1 I am expecting the answer as in lookup value-Answer expected column

Sheet1
Date ID Lookup Value-Answer expected 
1-Sep 103 103
1-Sep 102 102
1-Sep 101 NA
4-Sep 105 105
4-Sep 106 106
5-Sep 101 NA
5-Sep 104 NA
5-Sep 106 NA
5-Sep 199 199
5-Sep 200 200


Sheet 2

Date ID
1-Sep 102
1-Sep 103
4-Sep 105
4-Sep 106
5-Sep 199
5-Sep 200

2 Replies

Hi

 

Try:

=COUNTIFS(Sheet2!$A$2:$A$7,A2,Sheet2!$B$2:$B$7,B2)*B2

or:

=SUMIFS(Sheet2!$B$2:$B$7,Sheet2!$A$2:$A$7,A2,Sheet2!$B$2:$B$7,B2)

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies