In have a workbook with just 2 tabs. on tab 1 i have a long list (300K rows) and on tab 2 I have a long list (150K rows). I need to lookup the values in column A on tab 2 against column A in tab 1 and then show the value in column B but i have cases where i have more than 1 match in column A and i need to see all of the matches together on tab 2. so I have this formula which works 100% perfect
but... its slow and basically everytime i touch the sheet it locks up. I think there is a smarter way to handle with this index or maybe a smart vlookup but I know for sure this textjoin i have now isnt going to work. the reason I choose it was that if i have more than 1 match it nicely combined the values in to a single cell as seen here [login to view URL] but the workbook is not usable at this scale with this formula.
Need some quick help on this and ideally we can just replace what we have with your new formula and keep going on our way. the final output needs to be sure to handle these cases
no matches against tab 1, 1 match against tab 1, many matches against tab 1
Like answer here is something like pre sorting tab 1 and using OFFSET and INDEX and AGGREGATION
here is a sample sheet so you can see what i mean about the performance of this. I have chatted with several people about their ideas and I need to say for sure a solution here is not waiting 10 minutes for it to update and then copying static values.
[login to view URL]
42 freelancers estão ofertando em média $33/hora para esse trabalho
Hello, I can help you with your project, can you send me a message to discuss more details of the file attached?, I can start working on the best solution immediately.
Hi, I can get you the static values you need in about one minute. (copy paste and excel processing) It will require 2 helper columns in Tab 1. Is that OK? regards Reuben