Transposing values from one table to another, depending on column values

0
votes

I'm looking for a vlookup or macro (or anything really) that will allow me to achieve the following in 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.

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.

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.

So for example:

  • Cell A5 has the value "rural sheds"
  • Cell B5 has the value "1000"
  • Cell C5 has the value "50"
  • Cell D5 has the value "5%"
  • Cell E5 has the value "1.7"

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

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.

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

Cheers, Sam

1
excelexcel-formulavba

1 Answers

0
votes

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

=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:

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