Jan 20 2023 06:01 AM - edited Jan 20 2023 06:03 AM
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.
Jan 20 2023 06:27 AM
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)))
Jan 20 2023 08:12 AM
@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!
Jan 20 2023 09:33 AM - edited Jan 20 2023 09:36 AM
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.
Jan 20 2023 09:40 AM
@Patrick2788 I fixed those and that worked to correct NUM error - any idea on why I'm still returning the wrong "automation" name? TIA
Jan 20 2023 10:00 AM
Jan 20 2023 11:01 AM - edited Jan 20 2023 11:04 AM
It seems to be working my sheet.
Here's the formula again:
=INDEX(automation!$B$1:$B$4309,SMALL(IF(TRUNC(automation!$A$2:$A$4309,0)=A2,ROW(automation!$B$2:$B$4309)),COUNTIF($A$2:A2,TRUNC(A2,0))))
Jan 20 2023 11:06 AM
@Patrick2788 hmm ...I am getting this: EM-AD-WP-UG-WEEK3-247Tutoring
It should be EM-CR-GRUG-HCL_Tech_Opportunities-20220112
Jan 20 2023 11:37 AM - edited Jan 20 2023 11:38 AM
This is the tricky thing with using the date (w/dupes) as the lookup value. The first time the INDEX formula encounters the date 1/12, for example, it's pulling the 1st 'return' across from 1/12 on the other sheet. The next time 1/12 is found, INDEX fetches the second return and fourth. The desired return for your example is the 4th 1/12 from the top in the other sheet.