help with lookup formula

Copper Contributor

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. 

jklemm1_0-1674223388413.png

 

jklemm1_1-1674223412421.png

 

 

9 Replies

@jklemm1 

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 

 

jklemm1_0-1674236389725.png

 

Ctrl+Shift+enter to calculate as an array.
hmmm - I did that and nothing changed - suggestions...?

@jklemm1 

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))))

 

@Patrick2788 hmm ...I am getting this: EM-AD-WP-UG-WEEK3-247Tutoring

jklemm1_0-1674241459168.png

It should be EM-CR-GRUG-HCL_Tech_Opportunities-20220112

@jklemm1 

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.

 

Patrick2788_0-1674243181295.png