# How can I search across Excel sheets?

I want to transfer data automatically from 2 sheets to one single combining one. That means I have 2 different export sheets (called 'Te_open' and 'Te_closed') that contain both the columns "Problem" (in different positions, can vary always). I have one sheet, that represents an overview of the data from the two other export sheets. So when I have for example the value "Te1" in the cell A2 of my overview sheet, I want Excel to search in the two export sheets the value "Te1" and give back the value in the same row as "Te1" but of the column "Problem", so that I can fill the cell B2 where I want to have the Problem type. So I thought I might need the "MATCH" formula, to let excel know I want to search the column with the headline "Problem" in the export sheets, to fill the column "Problem" in my overview sheet.

The problem that I see is that I have to search TWO sheets and I don't know in which one the "Pm1" will appear. Furthermore I don't know in which column of the sheet "Problem" will appear.

Can you please help me? I would preferably like to solve the problem with a formula, not a macro. Thank you very much in advance.

Kind regards, question

## Answers

To find the index of the first column in one of your sheets that is headed 'Problem', you can use (e.g.):

=MATCH("Problem",Te_open!$1:$1,0)

Let's assume we place that formula in a cell somewhere and name the cell open_col. To find a value in column A of Te_open and return the corresponding value from the 'Problem' column, you can then use:

=VLOOKUP(A2,Te_open!$A$2:$Z$999,open_col)

Note that you'll have to define the search range to include the largest range that your data on Te_open could occupy. If the search value is found this will return the corresponding value from the 'Problem' column, otherwise it'll return #N/A. So if you've also set up a cell called closed_col to get the column index for the Te_closed sheet, you can do:

=IF(ISNA(VLOOKUP(A2,Te_open!$A$2:$Z$999,open_col)),VLOOKUP(A2,Te_closed!$A$2:$Z$999,closed_col),VLOOKUP(A2,Te_open!$A$2:$Z$999,open_col)

That gets the result from the correct column of Te_open if the search value was found there, otherwise it gets the result from the correct column of Te_closed. If neither sheet contains the search value it'll return #N/A.