Forum Discussion
Excel Vlookup / IF / Textbefore
mathetes
My friend, THANK you. Nice to know I am not the very oldest goat working in this stuff (still hanging on to my 60's).
I knew this was going to be a wild one and I just wanted to see if anyone who looked at it went "Oh yeah, just do THIS".
I am going to have to go the route of a picot table to get everything where I can see it. The biggest challenge is that this is all LIVE data so I cannot just freeze anything and then move it around. On GO-LIVE day, this will be an export to CSV and an upload to the database CRM and then I am going out for a Martini or two.
Again my friend, thank you for taking a look and I do so appreciate you time, Happy 2023!
This may work for you. I see you have access to TEXTBEFORE so you also have XLOOKUP (No need to involve 24+ columns with VLOOKUP).
The formula is a bit longer but more flexible:
=LET(data,$M$2:$AM$10000,store,TEXTBEFORE(INDIRECT("J"&ROW()),"-")*1,col,XMATCH("*"&INDIRECT("K"&ROW())&"*",$M$1:$AM$1,2),XLOOKUP(store,TAKE(data,,1),CHOOSECOLS(data,col),""))- You have used a number of functions I have never used and I hate to appear just plain stupid, but I have to ask:
The items you show in white in your formula (Store | Data | data,col): These appear to be references that I need to add. Am I correct? And if so, I need a bit of a better understanding as to what I am looking for for each reference.
I am sorry for having to bug you with this, and Thank You in advance!I recommend going through the Excel Jet link mathetes posted. The site starts with a very basic example, but you'll want to keep digging a bit to get to more sophisticated uses of LET.
Here is another good source:
Chris -- those references in the formula Patrick2788 gave you are references that are "created" in the course of using the LET function. The LET function is both a lot of fun, AND a very powerful tool in the new arsenal of Excel features. Well worth learning.
So it's the LET function that you need to familiarize yourself with. Here's a link.
- Going to give it a try and Thank You!
