Formula to check multiple cells for multiple values...

[Charmwah]

Hi all

I essentially would like a simplified version of the following:

=IF(OR(BF11="H",BJ11="H",BN11="H",BR11="H",BV11="H",BZ11="H",CD11="H",CH11="H",CL11="H",CP11="H",BF11="ü",BJ11="ü",BN11="ü",BR11="ü",BV11="ü",BZ11="ü",CD11="ü",CH11="ü",CL11="ü",CP11="ü",BF11="üü",BJ11="üü",BN11="üü",BR11="üü",BV11="üü",BZ11="üü",CD11="üü",CH11="üü",CL11="üü",CP11="üü"),$EY$10,"")

The purpose of the formula is to check the various cells for any of 3 text values. If any of these are found, the cell in which this formula exists is to return the value denoted by another cell.

Preferably i'd like to do this without the need for VBA

Thanks in advance!

 

[pgc01]

Hi
Welcome to the board

This seems to be some kind of table, you are checking every 4th value in a row.

It would help if the table has some type of header that is common to these columns. It would make the formula much more efficient.

If not, you can address directly the cells, like:

=IF(OR(T(OFFSET(BF11,,4*{0,1,2,3,4,5,6,7,8,9}))={"H";"ü";"üü"}),$EY$10,"")

 

[Charmwah]

Hi
Welcome to the board

This seems to be some kind of table, you are checking every 4th value in a row.

It would help if the table has some type of header that is common to these columns. It would make the formula much more efficient.

If not, you can address directly the cells, like:

=IF(OR(T(OFFSET(BF11,,4*{0,1,2,3,4,5,6,7,8,9}))={"H";"ü";"üü"}),$EY$10,"")

Thanks for the quick response! It is indeed a table, and there are headers, but the vary so to my mind are of no more use than the cell reference. In most cases it will be every 4th column to check (this represents 4 different product variants), but in some instances this value will be different.