Hello
I use this formula to check if name exist 1 or more times (see pic 1)
And if it exist it will add sign "!" to the name in the output.
Formula in the cell: E68
Names in cell (1): D68
Range for checking (2): D7:H12
Now I added new parameter for checking, Range I7:I12 and use this formula:
that will check if in the range I7:I12 exists word "Canceled" the name in the same row stay without sign "!" in output (see pic 2)
Now if I will add the same name in another row in the same range D7:H12, it will add again sign "!" to the name in output (see pic 3)
My formula working perfectly, but my question is if I can do it in another way and get more elegant and shorter formula ?
I tried with vlookup and xlookup, but without a correct result... maybe I used it wrong...
Thanks
I use this formula to check if name exist 1 or more times (see pic 1)
Excel Formula:
=IF($D68="","",IF(COUNTIF($D$7:$H$12,"*"&$D68&"*")>=1," "&$D68&" !"," "&$D68))
And if it exist it will add sign "!" to the name in the output.
Formula in the cell: E68
Names in cell (1): D68
Range for checking (2): D7:H12
Now I added new parameter for checking, Range I7:I12 and use this formula:
Excel Formula:
=IF($D68="","",IF(AND(COUNTIF($D$7:$H$7,"*"&$D68&"*")>=1,$I$7="")," "&$D68&" !",IF(AND(COUNTIF($D$8:$H$8,"*"&$D68&"*")>=1,$I$8="")," "&$D68&" !",IF(AND(COUNTIF($D$9:$H$9,"*"&$D68&"*")>=1,$I$9="")," "&$D68&" !",IF(AND(COUNTIF($D$10:$H$10,"*"&$D68&"*")>=1,$I$10="")," "&$D68&" !",IF(AND(COUNTIF($D$11:$H$11,"*"&$D68&"*")>=1,$I$11="")," "&$D68&" !",IF(AND(COUNTIF($D$12:$H$12,"*"&$D68&"*")>=1,$I$12="")," "&$D68&" !"," "&$D68)))))))
that will check if in the range I7:I12 exists word "Canceled" the name in the same row stay without sign "!" in output (see pic 2)
Now if I will add the same name in another row in the same range D7:H12, it will add again sign "!" to the name in output (see pic 3)
My formula working perfectly, but my question is if I can do it in another way and get more elegant and shorter formula ?
I tried with vlookup and xlookup, but without a correct result... maybe I used it wrong...
Thanks