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
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
Related Conversations
Hidden Names are Displaying
KCMFCC in SharePoint on
2 Replies
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies
Setting Date in Stored Procedure
Tim Hunter in SQL Server on
1 Replies
Separate lines on Sheets depending on condition
Mohammed Almaghi in Excel on
1 Replies
Document Lookup Column Link
Phil_COB in SharePoint on
8 Replies
if then formula help
BMARSH99 in Excel on
1 Replies