Forum Discussion

JennyHoA20181's avatar
JennyHoA20181
Brass Contributor
May 29, 2025

Date Lookup based on multiple columns in separate sheet

Hello all,

Hoping someone can support!

In the 'Date' tab, I want to add another component to the formula in column C. Currently it brings back the first date from Column X of the 'Activity-Risk' Tab.

I also want it to only bring back the date in Column X where 'Activity' (column O) = Conversion Support and 'Activity Detail' (column P) = Arrange Engagement Call OR Arrange Interview.

 

Tricky!

 

Thank you!

excel
Formulas and Functions

8 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 29, 2025

    Since this is a large data set, I think the best approach is to perform an initial FILTER to pull relevant records to help make the XLOOKUP more manageable.

    =LET(
    filtered, FILTER(
        HSTACK(AppID, date),
        (Activity = "Conversion Support") * (ActivityDetail = "Arrange Engagement Call") +
            (ActivityDetail = "Arrange Interview"),
        "None"
    ),
    lookup, TAKE(filtered, , 1),
    return, TAKE(filtered, , -1),
    XLOOKUP(all_app_ID, lookup, return, "", , {1, -1})
)

     

    • JennyHoA20181's avatar
      JennyHoA20181
      Brass Contributor
      May 30, 2025

      I have uploaded another excel, this time containing all columns I am using in my original spreadsheet. The column where your formula needs to be is in column BG of tab called 'Application Base'. Columns to the right need to be there, so maybe that is what us affecting it?

    • JennyHoA20181's avatar
      JennyHoA20181
      Brass Contributor
      May 30, 2025

      Thank you Patrick! I  get an error that says #SPILL! on my side, could it be my excel version? I am using Xlsb

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        May 30, 2025

        The cells below the formula are occupied or you're working in a table is my guess. 

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    May 29, 2025

    here is a formula to filter the list based on those criteria and then take the 1st value:

    =TAKE(FILTER('Activity-Risk'!X:X,('Activity-Risk'!A:A=A2)*('Activity-Risk'!O:O="Conversion Support")*(('Activity-Risk'!P:P="Arrange Engagement Call")+('Activity-Risk'!P:P="Arrange Interview")),""),1)

    in your sample sheet that column A (application_id) doesn't match any value in columns A (nor B) on the Activity-Risk tab so no values are found.

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        May 29, 2025

        so it is doing over 18K filters on over 36K rows of data so when you did a fill down I imagine it may have taken a bit but going forward it shouldn't be as bad.  But that said a number of improvements could be made like only referring to the range where data exists.  For example if you convert the Activity-Risk data into a Table (Home -> Format as Table) then you can use table structured references to make it more efficient.  Doing that and the calc took <1min for me (but I didn't check how long without it).  Another option might be how that list of application_id values are pulled.  Does this have to be a lookup or can it filter the list of application_id and for each line check the conditions.  So think instead of 18K times looking through the list of 36K items to find the match it goes the the 36k list 1x and if it meets some condition it checks a couple other items and then spits out the corresponding rows and values.  

        Another option I tried was using XLOOKUP because you only need the 1 value so in theory XLOOKUP can find the answer and stop while FILTER would go through the whole list each time.  

        see the attached file for the updated examples.

Resources