Search code examples
google-sheetsconditional-formatting

Conditional formating depending of two values in other tab (google Sheets)


I am creating a database in which the staff is listed (tab named "Sheet1"). This database includes: a locker room(col A), a locker number (col B) and the name of the person (col C).

enter image description here

In another tab (named "Sheet2"), I have created a multi-column table that includes all the locker room and locker numbers concatenated with _.

enter image description here

If the name of the person is "Test", i would like the corresponding box of the table is put in red like that :

enter image description here

So I tried the formula below but I can't get what I want :

=ArrayFormula((B2<>"")*(RECHERCHEV(REGEXEXTRACT(B2;"\d+")&"_"&REGEXEXTRACT(B2;"\d+");{INDIRECT("Sheet1!A:A")&INDIRECT("Sheet1!B:B")\INDIRECT("Sheet1!C:C")};3;FAUX)="Test"))

Can you help me with that please ?


Solution

  • you may try this custom formula within Conditional Formatting.

    =XMATCH(A1;(FILTER(INDIRECT("Sheet1!A:A")&"_"&INDIRECT("Sheet1!B:B");XMATCH(INDIRECT("Sheet1!C:C");"Test"))))
    

    enter image description here