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

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)

 

Highlighted