0

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
1

You can use COUNTIFS in an IF:

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),B2,NA())

So if the combination is found it returns the value or an error.

enter image description here

  • This formula is generating IDs in sequence if using in another location, please check ! – Rajesh S Sep 28 '18 at 6:36
  • 1
    Not sure I understand your concern. It returned exactly the value the op wanted. – Scott Craner Sep 28 '18 at 6:42
  • I'm using it in Sheet1 C81,,,, and getting ID in sequence rather then the Screen Shot's display ! – Rajesh S Sep 28 '18 at 7:14
  • without seeing your setup and the formula you are using, I cannot begin to guess why yours does not match mine. – Scott Craner Sep 28 '18 at 7:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.