将值从一个表转换为另一个表，具体取决于列值

I'm looking for a vlookup or macro (or anything really) that will allow me to achieve the following in Excel.

我正在寻找一个vlookup或宏(或任何真正的东西),这将允许我在Excel中实现以下功能。

I have a table with columns A through E. Column A is a "Query" (just a phrase that people are using to search for a client's website in Google). Columns B through E provide data on impressions, click thru rate etc.

我有一个包含A到E列的表。列A是一个“查询”(只是人们用来在Google中搜索客户网站的短语)。列B到E提供有关展示次数,点击率等的数据。

I also have a table, starting with column, G ("Word/Phrase"), sitting in the same sheet. Column F is blank. Columns H through K have the same headings as columns B through E on the first table.

我还有一个表,从列G开始(“Word / Phrase”),坐在同一张纸上。 F列是空白的。列H到K具有与第一个表上的列B到E相同的标题。

What I want to do is scan column A ("Query") and if one of the results in column A EXACTLY matches a result in column G ("Word/Phrase") then pull the data from the respective cells in columns B to E and paste it into columns H to K.

我想要做的是扫描A列(“查询”),如果A列中的一个结果与G列中的结果完全匹配(“Word / Phrase”),则从B列到E中的相应单元格中提取数据并将其粘贴到H到K列中。

So for example:

例如:

Cell A5 has the value "rural sheds"

细胞A5具有“农村棚屋”的价值

Cell B5 has the value "1000"

单元格B5的值为“1000”

Cell C5 has the value "50"

Cell C5的值为“50”

Cell D5 has the value "5%"

单元格D5的值为“5%”

Cell E5 has the value "1.7"

单元格E5的值为“1.7”

And in G17 the value is "rural sheds" ... now it's just a case of getting that other data transposed across.

在G17中,价值是“农村棚屋”......现在只是将其他数据转换成一个案例。

I've Googled for an hour or so but can't seem to find anything that works. Obviously I can do it all by hand using CTRL + F, but I'll be doing lots of these reports and would love to save time.

我用谷歌搜索了一个小时左右,但似乎找不到任何有用的东西。显然我可以使用CTRL + F手动完成所有操作,但我会做很多这些报告,并希望节省时间。

I'm using Excel for Mac 2011, if that means anything.

我正在使用Excel for Mac 2011,如果这意味着什么。

Cheers, Sam

1 个解决方案

#1

Use this one in H1 and drag it down and accross:

在H1中使用这个并将其向下拖动并交叉:

=INDEX(B:B,MATCH($G1,$A:$A,0))

If exact match not found, formula above returns #N/A, but you can use next one to handle error:

如果未找到完全匹配,则上面的公式返回#N / A,但您可以使用下一个来处理错误:

=IFERROR(INDEX(B:B,MATCH($G1,$A:$A,0)),"not found")

#1