How to highlight the 2 closest values in a list to a given number in Google Sheets?

Hi delle,

Thank you for sharing the method, and sample file, they helped make the working solution.

To get the formula working in Google Sheets, it just needs to be wrapped in ARRAYFORMULA().

Here is the final solution (see your file for example):

Please Login or Register  to view this content.

ARRAYFORMULA is required when checking multiple values such as the $B$6:$Z$6

For older versions of Excel, the solution for dealing with arrays is to wrap the formula in {curly brackets}

The formula in the article does not mention that, perhaps because it is expected that users of older versions of Excel would already know about that.

Heres another example... lets say you have an XLSX file with many complicated formulas, if you upload and convert that file to Google Sheets, it is possible that one or more formulas may be wrapped in =ARRAY_CONSTRAIN(ARRAYFORMULA())
This is Google Sheets recognising the formula is an array type, and it makes the necessary change.
HOWEVER, the outer ARRAY_CONSTRAIN is almost never needed (i can recall ONE time it was required), so it can be removed, just leaving the ARRAYFORMULA()

Whenever trying to get an Excel formula working in Google Sheets, if it doesnt work straight away then a good first step in troubleshooting it is to try wrapping the formula in ARRAYFORMULA().