Forum Discussion
help with lookup formula
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.
Patrick2788 I fixed those and that worked to correct NUM error - any idea on why I'm still returning the wrong "automation" name? TIA
- Ctrl+Shift+enter to calculate as an array.