Exceljet

Quick, clean, and to the point

Lookup entire row

Excel formula: Lookup entire row
Generic formula 
=INDEX(data,MATCH(value,array,0),0)
Explanation 

To lookup and retrieve an entire row, you use the INDEX and MATCH functions. In the example shown, the formula used to look up all values for the Central region is:

=INDEX(C5:F8,MATCH(H5,B5:B8,0),0)

How this formula works

The core of this formula is to use the MATCH function to identify the row index, and the INDEX function to retrieve the entire row by setting the column number to zero. Working from the inside out, MATCH is used to get the row index like this:

MATCH(H5,B5:B8,0)

The lookup value "Central" comes from H5, the array is the range B5:B8, and zero is used to force an exact match. The MATCH function returns 3 as a result, which goes into the INDEX function as the row number:

=INDEX(C5:F8,3,0)

With the range C5:F8 for array, and 3 for row number, the final step is to supply zero for column number. This causes INDEX to return all of row 3 as the final result, in an array like this:

{116000,129250,127250,142500}

Processing with other functions

Once you retrieve an entire row of data, you can feed that row into functions like SUM, MAX, MIN, AVERAGE, LARGE, etc. for further analysis. For example, to get the smallest value in the Central region, you could use:

=MIN(INDEX(C5:F8,MATCH(H5,B5:B8,0),0))

Multi-cell array formula

To return results to more than one cell, enter as a multi-cell array formula.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables