如何使用vlookup从合并的单元格创建结果表 [英] How to create result table with vlookup from merged cells

问题描述

我有这个数据表，我想要另一个结果表。当我写状态时，结果表可以显示所有公司的data1，data2和data3.I尝试使用vlookup，但因为合并的单元格公式只显示第一行。
如何解决问题？

解决方案

如果我正确理解，您要设置一个查找范围，以便在您输入在特定状态下，您可以看到所有在该州拥有数据的公司的数据。这是一种方法。

您首先需要做的是在原始表格的左侧设置三列：

• 第一列包含与每行数据相关联的状态名称


• 第二列是一个索引，每个状态下的数据行数量


• 第三个组合了前两列，为表中的每一行生成一个唯一的键值。

这三列中的所有值都可以通过公式分配。下图显示了第一行单元格 A9：C9 的公式，然后通过第27行向下复制。

下一个在我的例子中，一步是布置新的表格，它在单元格 Q8：U27 中。

关于设置的几件事情要注意。首先，将显示的状态输入单元格 Q9 ，我以黄色突出显示。在表的左侧，在P列中，我输入了从1到19的项目编号，这将需要构建查找的键值。查找公式本身在单元格 R9：U27 ;在图中，显示了第一行的公式（ R9：U9 ）（然后它们通过第27行复制）。

值得花一点时间仔细查看其中一个查找。以下是单元格R9中第一个公司名称的公式。

  = IFERROR（VLOOKUP（$ Q $ 9& $ P9，$ C $ 9：$ N $ 27,4,0），）
  

VLOOKUP 的论据反过来， $ Q $ 9& $ P9 连接单元格 Q9 与项目编号（在这种情况下为1），产生查找值California1。查找表定义为范围 $ C $ 9：$ N $ 27 - 该范围的列C是查找值匹配的列。第三个参数是如果查找匹配，则返回值的列。这里的数字4对应于原始表的公司名称列。最后，最后一个参数为0（或等效地为FALSE），表示需要精确匹配。

最后， VLOOKUP 函数包裹在 IFERROR 中。这样就可以获得在没有匹配的情况下返回的＃N / A ，用空字符串（）替换。

I have this data table and i want another result table. when I write name of state ,result table can show all of company with data1,data2 and data3.I trying use vlookup but because there are merged cells the formula just show first row. how can I fix problem?

解决方案

If I'm understanding correctly, you want to set up a lookup range so that when you enter a particular state, you can see the data for all the companies that have data in that state. Here is one way to do that.

The first thing you would need to do is set up three columns to the left of the original table:

• The first column holds the name of the state associated with each row of data
• The second is an index that counts off the number of data rows in each state
• The third combines the first two columns to produce a unique key value for each row in the table.

All the values in these three columns can be assigned by formula. The picture below shows the formulas for the first row of cells A9:C9, which are then copied down through row 27.

The next step is to lay out the new table, which is in cells Q8:U27 in my example.

There are several thing to note about the setup. First, the state that will be displayed is entered in cell Q9, which I've highlighted in yellow. To the left of the table, in column P, I've entered item numbers from 1 to 19, which will be needed to construct the key values for the lookups. The lookup formulas themselves are in cells R9:U27; in the picture, the formulas for the first row (R9:U9) are shown (they are then copied down through row 27).

It's worth taking a moment to look more closely at one of the lookups. Here is the formula for the first company name in cell `R9'.

  =IFERROR(VLOOKUP($Q$9&$P9,$C$9:$N$27,4,0),"")

Looking at each of the arguments of the VLOOKUP in turn, $Q$9&$P9 concatenates the state name in cell Q9 with the item number (1 in this case), yielding the lookup value 'California1'. The lookup table is defined as the range $C$9:$N$27 - column C of that range is what the lookup value is matched against. The third argument is the column from which to return a value if the lookup is a match. The number 4 here corresponds with the company name column of the original table. Finally, the last argument is 0 (or equivalently, FALSE) indicating an exact match is required.

Finally, the VLOOKUP function is wrapped inside IFERROR. This catches the #N/A that would otherwise be returned when no match is found, replacing it with an empty string ("").

这篇关于如何使用vlookup从合并的单元格创建结果表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！