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.

List 1	Sold			List 2	Searches			conversion
Apples	10			Pineapples	100			0
Oranges	15			Bananas	150			3.33%
Pears	20			Apples	125			8.00%
Bananas	5			Lemons	200			0

<tbody>
</tbody>

 

[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!