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?
Hi @Naos_Wilbrink,
I wouldn’t say that index-match is better than vlookup. It depends on what you want to achieve. index-match is more versatile, that’s true.
It is easier for me to actually see the worksheet to analyse formulas, but is seems to me that:
- the size of your arrays don’t match (are you getting an error message)
- if the range in index() is a rectangle, then you probably need index-match-match
My apologies if I am totally off. I would need to see the spreadsheet.
2 Likes
@Naos_Wilbrink
Also, silly question perhaps, but have you tried in Glide’s data editor creating a relation column followed by a lookup?
Yeah for many thing a great solution, but I’m actually using the collection from a join in glide in a user specific option selector and that collection doesn’t include any on the lookup values. But now I’m typing that, maybe I should give it another go.
In general though, started the topic in the idle hope that I might have done something wrong with the arrayformula with index and match and that someone could tell me another way to not use vlookup 
I’ll put it in a different sheet and share.
I second the relation an lookup method, unless you specifically need the value in the google sheet for some reason. It’s faster and easier than trying to do it in the sheet.