Forum Discussion
help with lookup formula
Hello! I am on windows running excel 2016.
I am trying to create a formula that helps me with a lookup from the first set of data to the second.
I need to pull the automation name next to the send name. I have attached a workbook with the data examples. I only know v-lookup for simple matches. The dates and part of the name are the same but I am unsure of how to get that into the proper lookup formula to pull the correct info over. Thank you in advance.
9 Replies
The issue with the lookup is one sheet has date & time and another has date.
This formula will make sure both have date only and then it will pick through multiple returns for a given date where needed.
In Excel 2016, press ctrl+shift+enter to define formula as an array:
=INDEX('automation report '!$B$1:$B$7,SMALL(IF(TRUNC('automation report '!$A$2:$A$7,0)=A2,ROW('automation report '!$B$2:$B$7)),COUNTIF($A$2:A2,A2)))Patrick2788 Hello - thank you for this response! I applied this to my actual workbook but I am still returning incorrect results. Can you take a look t this notebook and see where I may be going wrong in applying your formula? thanks!
It looks like the NUM errors are coming from the fact a few of your dates in the 'send' sheet have date and time not just the date. The best solution is to fix the dates because we can't nest TRUNC in the COUNTIF for the range.
