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

Related posts:

1. Combine data from multiple sheets in excel