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?
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
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.
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Checks whether a value is an error and returns TRUE or FALSE
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Returns the relative position of an item in an array that matches a specified value
Returns the reference specified by a text string