How to find closest match

How to find closest match

This article shows How to find closest match by using INDEX, MATCH, ABS and MIN functions. Excel’s array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =INDEX( return array,      MATCH(         MIN(ABS( search...
How to find errors in a list

How to find errors in a list

This article shows how to find errors in a list by using ISERROR and MATCH functions in an array formula. Syntax { =MATCH(TRUE, ISERROR(search array), 0) } Steps Start with =MATCH( function Type TRUE, variable to search for the matched values Continue with ISERROR(...
How to make case sensitive match

How to make case sensitive match

If your data is case sensitive, you may have issues finding correct matches with lookup functions. This How to make case sensitive match article shows you how to combine MATCH and EXACT functions to achieve case sensitive matching. Syntax { =MATCH(TRUE, EXACT(search...
How to return an entire row

How to return an entire row

To return an entire row you need to use array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET. How to return an entire...