Search three columns on three sheets, part 2

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Search three columns on three sheets, part 2

← Previous post -

Filed in Excel on Mar.19, 2009. Email This article to a Friend

In the previous article "Search three columns on three sheets", I created a simple but effective search formula with userfriendliness in mind. The user can choose what sheets and what columns to search without changing the acual formula.

This article will enhance the formula to find ,if possible, more than one matching item. The formula will also display a user chosen column value on the same row as the matching item. It might be the adjacent value or a value further away but always on the same row as the matching search item. Hopefully the screenshots will put a clearer picture into your mind.

Sheet2 and sheet3 are only search value examples. The interesting stuff happens on sheet1.

Sheet2

Sheet3

Sheet1

Explaining sheet 1

Yellow cells are user input cells.

On row 3, the user can choose what sheets to search in.

Row 4, the user can choose what columns to search in.

Row 5, the user can choose what columns to return a value from.

The formula on row 6 (B6:C12)

=IF(ISERROR(SMALL(IF($B$1=INDIRECT(B3&"!"&B4&":"&B4),ROW(INDIRECT(B3&"!"&B4&":"&B4)),""),ROW()-5)),"",INDEX(INDIRECT(B3&"!"&B5&":"&B5),SMALL(IF($B$1=INDIRECT(B3&"!"&B4&":"&B4),ROW(INDIRECT(B3&"!"&B4&":"&B4)),""),ROW()-5))) + Ctrl + Shift + Enter

The downside with using column reference A:A and so on is the bad impact it has on calculation speed.

Next article takes into account the exact data ranges on every sheet and uses those ranges to speed excel sheet calculation.

Download excel sample file for this article.
search-three-multiple-columns-on-three-sheets-advanced
(Excel 97-2003 Workbook *.xls)

Functions in this article:

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

IF(logical_test,[value_if_true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference)
returns the row number of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Search three columns on three sheets

Lookup with multiple criteria and display multiple search results using excel formula, part 4