Google Sheets: Multiple search keys in Index Match

user_12283823 0
Viewed 374

enter image description here

Working on this sheet :

I want to basically find out "Last day worked" on a scheduling file "C" and "C2" Represent different type of shifts.

Row 2 has dates Row 16 has shifts

Basically searching for "C" or "C2" in Row 16 and returning results from Row 2 for the date

I am using this to get the result

=INDEX(O2:2,Match("C""C2",O16:16,+1))

But can only do one shift(search key i.e "C") using this

Is there a way to search for "C" and "C2" at the same time instead of using two columns and then determining the highest value ?

google-sheets-formula google-sheets
1 Answers

You can add a wildcard to the search key:

=INDEX(O2:2, Match("C*", O16:16,  0))

This will match for C and C2 but will only return the first match

If you'd want to return all matches, try

=FILTER(O2:2, REGEXMATCH(O16:16, "^C"))

To return the last match only, you can try

=IFERROR(INDEX(O2:2; MAX(FILTER(COLUMN(O2:2); REGEXMATCH(O16:16; "^C")))-COLUMN(O2)+1))

or

=+IFERROR(SORT(TRANSPOSE(FILTER({O2:2; COLUMN(O2:2)}; REGEXMATCH(O16:16; "^C"))); 2; 0))
user_38894230
Related