## Search three columns on three sheets, part 2

*Article updated on March 22, 2009*

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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article