Hi, what is a good Google sheet formula for INDEX and Match? How would it look. I want the cell to recognize the WEEKDAY, then return the TEXT associated with the WEEKDAY value. I have my WEEKDAYS in one column, then details in another. I’m not quite sure if using INDEX and MATCH are the correct formulas, and if so, I’m having trouble visualizing how it would look.
Are you looking for a google sheet formula or a way to do an index/match within glide? Relation and lookup would be your options in glide.
1 Like
you can do =text(a2,“ddd”) for both values to extract day names, and then mach them.
lol…I don’t know which one will work first. I’ve learned more about formulas, since using Glide (a very good thing). I’ve created my relation list and do see the value, but the whole LOOKUP formula is going to be a slight learning curve for the moment…lol I’m willing.
So given a date, you want the text representation of the weekday associated with that date, yes?
I would do that in Glide.
Assuming you have your Weekdays in a single column somewhere: Sunday, Monday, Tuesday, etc.
- Create a math column to get the numerical weekday from your date (
Weekday(date)-1) - Then use a Single Value column, taking X from the start of your Weekday column, where X is the output of the first column
3 Likes
It sounds as if, the numerical weekday is being turned into text. 07/21/2021 is WEDNESDAY, JULY 2021. This is not what I want. I want the text in a corresponding cell with the corresponding date to Match. So, when the date changes, the text from the corresponding cell appears. EXAMPLE: If it is Monday and it says, “Have a Good Day”, then when the day changes to Tuesday, the corresponding text will say, “It’s not over…” Basically, VLOOKUP, as @Jeff_Hager mentioned.
you want deferent greeting for a deferent day?
In that case, it’s exactly the same two columns, except you do the Single Value lookup on your list of quotes.
1 Like
Yes
you can use many functions to do that… match, choose, filter… fastest and most effective is vlookup
create column with weekdays names, and the other column with corresponding greetings,
then use
=arrayformula(if(c2:c="",,vlookup(c2:c,a2:b,2,false))
column A weekdays names, B text, C day to look for
you can convert C column to just days names
> =arrayformula(text(C2:C,"ddd"))
2 Likes
Would it matter if I have my weekdays using a formula, too? I used the UNIQUE formula to pull in the entry date of another column.
no, just be in the same format
Couple of things:
First, I wasn’t referring to a vlookup. I was referring to a lookup column in glide. In fact, what I’m picturing doesnt require any google sheet formulas. Everything would be within glide, calculate instantly and dynamically for each user. @Darren_Murphy’s idea with a Single Value is probably the quickest way to do it. I was thinking of obtaining the weekday with match column, a single relation that uses that number a links to a table with your quotes (as long as they have a numbered column and a quote column), and a Lookup column to pull back that matching value. Darren’s method takes a couple shortcuts to my method and is more efficient.
4 Likes
@Jeff_Hager is right, if you are looking for instant change, then do it in the Glide columns, if you wanna comfort of GS and don’t mind 3-5 sec delay to change that message… then use GS formulas. all techniques have some pluses and minuses.
2 Likes
Thank you @Jeff_Hager @Darren_Murphy, and @Uzo. I’ll work with it and see what happens, as I learn as I go… 
1 Like