Oct 24 2019 07:04 AM
Oct 27 2019 04:57 AM
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
Oct 27 2019 05:50 AM
Oct 27 2019 07:21 AM
And lookup Worksheet2!B1 at any place of the Worksheet3 or in some specific range?
Oct 27 2019 08:38 AM - edited Oct 27 2019 08:50 AM
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) )
Oct 27 2019 09:21 PM