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).

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

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

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 ?

CodePudding user response：

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"))))