Forum Discussion
Date Lookup based on multiple columns in separate sheet
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.
Thank you so much!
Sorry about that, I've now added the remaining ids. Rolled the formula down, but perhaps I'm doing something wrong? The calculating threads took a good 15 min!
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.
