How do I search across sheets in Excel

Question

I want to transfer data automatically from 2 sheets to one single combining one. That means I have 2 different exports that contain both the columns "Problem". I have one sheet, that represents an overview of the data. So when I have for example the value "A333" in A1 of my overview sheet, I want Excel to search in the two other export sheets the value "A333" and give back the value in the same row as "A333" but of the column "Problem" not "A".

The problem that I see is that I have to search TWO sheets and I don't know in which one the "A333" 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.

Answer 1

1) write a formula (VLOOKUUP() for example) that looks for the value in the first sheet

2) write a formula that looks for the value in the second sheet

3)since VLOOKUP returns an ERROR if a value is not found you can combine the formulas together using IFERROR(). for example: =IFERROR(VLOOKUP(ref_cell,sheet1_range,2,0),VLOOKUP(ref_cell,sheet2_range,2,0))

---

EDIT:

It looks like you did not provide all the pertinent information in your question! I suggested VLOOKUP(), but this will only work if you know which COLUMN the value you are looking for will appear in, but you said this is not the case...

using you comments I therefore assume the following:

• the two test sheets are called "test1" and "test2"
• The reference value we are looking for is in cell A5 on the main sheet, cell D1 on the sheet contains "Problem" (text)
• The reference value may appear anywhere (I limit here to range A1:J100) on test1 or test2, but ONLY ONCE
• The column "Product" on test1 and test2 may appear in different columns, but it will always have a heading "Product" in row 3 (test1!A3:J3 and test2!A3:J3)

see below of screenshot of the answer (column C shows result, column D shows the underlying formula) If required use the information provided to create a single-cell formula (because Stack Overflow is not a we-write-the-answer-for-you service)