## Search three columns on three sheets

*Article updated on March 18, 2009*

**Question: **I want to search columns on multiple sheets, is there a search formula that is userfriendly if I want to change columns or sheet names in the future?

**Sheet2**

**Sheet3**

**Sheet4**

**Answer: **Yes, here is a userfriendly formula with sheet and column input. Yellow cells are editable user input cells.

B1: Search value

B3:D3: Sheet names

B4:D4: Column names

B5:D5: Search results

=IF(ISERROR(MATCH($B$1,INDIRECT(B3&"!"&B4&":"&B4),0)),"",MATCH($B$1,INDIRECT(B3&"!"&B4&":"&B4),0))

The downside with this formula is that if there are more than one matching value in a column. Only the first one:s row number will show up in the result cells (B5:D5). The next article will cover this problem and the problem with the retrieving of an adjacent cell value.

Download excel sample file for this article.

search-three-multiple-columns-on-three-sheets

(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

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

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