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.


Need Your Help

Can Matlab eliminate the path in URL and left only the domain part?

string matlab url path domain-name

Can Matlab eliminate the path in URL and leave only the domain part? Does Matlab have any function to eliminate the path behind?

Encoding binary data in a PNG?

python encoding png python-imaging-library steganography

I'm looking for information on how to encode a small amount of binary data (say, around 200 bytes) into a PNG through colors; essentially what Hid.im does for .torrent files. Any information or