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

search_columns_part2_sheet2


Sheet3

search_columns_part2_sheet3


Sheet1

search_columns_part2_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