如何在Excel中搜索表格 [英] How do I search across sheets in Excel
问题描述
我想将数据自动从2张传输到一个单独的组合。这意味着我有两个不同的导出包含列问题。我有一张表，表示数据的概述。所以，当我在我的概述表的 A1 中，例如$ A333时，我希望Excel搜索另外另外两个导出表的值A333，并将与$ code>A333相同的行返回值的列问题不是A。
我看到的问题是我必须搜索两张表，我不知道哪一个A333将出现。
1）写一个公式（ VLOOKUUP（））查找第一张表中的值
2）写一个寻找第二张表中的值的公式
3）由于VLOOKUP返回错误，如果没有找到值，您可以使用 IFERROR（）组合公式。例如： = IFERROR（VLOOKUP（ref_cell，sheet1_range，2,0）），VLOOKUP（ref_cell，sheet2_range，2,0））
编辑：
看起来你没有提供所有相关信息在你的问题！
我建议使用VLOOKUP（），但是只有当你知道你正在寻找哪个COLUMN时才会出现，但是你说的不是这样...
使用您的评论我因此假设以下：
- 两个测试表称为test1和test2
- 我们要查找的参考值是在主页上的单元格A5中，表格上的单元格D1包含问题（文本）
- 参考值可能出现在test1或test2上的任何地方（我在此限制范围A1：J100），但ONLY ONCE
- test1和test2上的Product列可能会出现在不同的列中，但是它将在第3行（test1！A3：J3和test2！A3：J3）中始终有一个标题Product。
下面的答案屏幕截图（列C显示结果，列D显示底层公式）
如果需要，使用提供的信息创建单个单元格公式（因为堆栈溢出不是我们写的答案 - 用于-Y ou service）
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.
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)
这篇关于如何在Excel中搜索表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
