Select Page

## How to get last match

This article shows how to get last match by using MAX, MIN, IF and ROW functions. Excel’s array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =MAX( IF( data = search value, ROW( data ) – MIN( ROW( data...

## 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

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

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 SUM 2d ranges with SUMPRODUCT

Summing values by a condition may sound easy. If not, please see Crunch Data Even Faster with the SUMIFS Function article as well. You can easily sum values by condition(s) with SUMIF and SUMIFS functions; with a limitation. The sum range and criteria ranges should...

## How to remove blanks from a list

We often find data in a raw, unorganized form that contain empty cells. In this article we’re going to show you how to remove blanks from a list and create a new table using only formulas. Syntax =IFERROR(INDEX(list range,SMALL(IF(ISTEXT(list range),ROW(rows...

## A New Dimension Building Excel Models: Array Formulas

Excel’s array formulas are indeed a fascinating subject. Although they can be very useful by making it possible to solve some complex calculations, and replace hundreds of formulas in certain situations, most Excel users have probably never used these formulas before...