Google Sheets (Formula) - If one cell equals some value, it will return another value to a neighbori-CodePudding

I am trying to find a formula for the following in Google Sheets:

If a cell (C4) has the value Mon, Tue, Wed, Thu, Fri, Sat, Sun. I want it to return a number to a neighboring cell (B4) - the number 1 for Mon, 2 for Tue, 3 for Wed, etc.
I have scoured the internet for this formula and have found nothing. Those who assist, your help is greatly appreciated.

CodePudding user response：

try in B4:

=INDEX(IFNA(HLOOKUP(B4; REGEXREPLACE(TEXT(SEQUENCE(1; 7; 1000001); 
 {"ddd"; "@"}); "^100000"; ); 2; )))

CodePudding user response：

or:

=INDEX(IFNA(MATCH(B4; TEXT(SEQUENCE(1; 7; 1000001); "ddd"); )))