Calculating from two tables where only unique record match

[cqcqcq]

I'm struggling on how to do this. I have list of search engine keywords, thousands of them with amount of searches. In another table I have search phrases that led to sales. Those two tables are not connected in any way from their source.

So my question is, how can I look up the unique phrase from one table, see if it exists in the other, then calculate based on only matching records?
Below, and I hope it keeps its formatting, is a simple example. List 2 has searches, and only two of them match what is in list one. Therefore, those two get calculated for a conversion number.

I have the Fuzzy Match plugin, but I feel there's a better way to do this without using that.


[TABLE="width: 612"]
<tbody>[TR]
[TD]List 1[/TD]
[TD]Sold[/TD]
[TD][/TD]
[TD][/TD]
[TD]List 2[/TD]
[TD]Searches[/TD]
[TD][/TD]
[TD][/TD]
[TD]conversion[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pineapples[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bananas[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.33%[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lemons[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Hi there, this should do the trick:

Assuming your data start in column A1, put this formula in E2 (the conversion percentage for the Pineapples line):

=IF(ISNA(VLOOKUP(C2,A:A,1,FALSE)),0,VLOOKUP(C2,A:B,2,FALSE)/D2)

then copy down. Set the cell format to percentage.

To explain, the first part sees if there is a match and returns 0 if not. If there is a match, the second vlookup returns the sold value and calculates the percentage from that.

Hope this helps.

John

 

[cqcqcq]

John that worked perfect!! Thank you so much you just made my day!

 

[jmacleary]

You are very welcome. Enjoy the day!