Hi all! I’m looking for some of your smartness.
I have very ugly solutions in my sheet. Because of the Vlookup in many places I had to double some key data (copy the exact data from column A in column AR). Because of the Vlookup I can clean up my sheets of unused columns that no longer served a purpose.
So I read about using the Index and Match formulas being better than vlookup (faster, cleaner, and all relative). After some time struggling and trying to see if I made a simple error I read that Index and Match can’t work in an array formula
=ARRAYFORMULA(if(A2:A<>"",INDEX(Challenge!$B$1:$G$6,MATCH(D2:D, Challenge!G:G, FALSE),1) ,""))
It simply looks up a friendly name for a Challenge that I need in this collection to show in a relationship in glide --> presenting the user with a custom dropdown selection. (showing them the challenges they are enrolled in). --> Also the reason why I need to do it in Sheets rather than glide.
Any suggestions other than a Vlookup?