Complex conditional formatting

[Dunk130971]

Hi, I'm looking for some help on a complex problem. I have a workbook with multiple worksheets.
I want to lookup a cell, B1 on worksheet 2 in worksheet 3, if found I want to look at the value in the cell immediately to the right and if value of that cell=1, then format background colour of cell B1 on worksheet 2 to red. If the value=2 then format blue and if the value=3 then format B1 worksheet 2 orange.
Thanks in advance

[#2]

@Dunk130971 

Can you clarify what you mean by "if found" in this sentence

I want to lookup a cell, B1 on worksheet 2 in worksheet 3, if found I want to look at the...

Thanks

[#3]

Hi Wyn
It should probably say "when found"
Thanks
Duncan

[#4]

@Dunk130971 

And lookup Worksheet2!B1 at any place of the Worksheet3 or in some specific range?

[#5]

@Dunk130971 

That would be the famous 2D ...IFS trick …  OK, so I exaggerate, the almost unheard of use of MAXIFS.  The ...IFS functions will search a 2D range for a value (or an array of values) and return the contents from the matching positions in a further similarly-dimensioned range.

By overlapping the ranges (I have defined them to be 'landingArea' and 'offsetArea') with a single cell offset, the formula will return the contents of the cell immediately to the right of any matched cell.

Unfortunately, conditional formatting is a piece of stone-age junk in that it will not use an array as the criteria to format a range, never mind to determine the extent of the CF range.  Hence I have used INDEX to format each cell individually by relative referencing using

= (format = 1)

etc. where 'format' is given by

= MAXIFS( offsetArea, landingArea, INDEX(input, k) )

[#6]

Sorry Duncan, still not sure, what are you trying to find here?