Large, Small, Index, Match formula combos

**taudano** · 06-29-2011, 08:01 PM

Hello,

Attached is a sample spreadsheet. The first tab is where I am entering the formulas and the second tab is the data set.

My goal is to automatically find the top 5 rising and top 5 falling keywords without having to figure it out manually. One the top 5 rising and falling positions have been identified, I want to display the corresponding keyword, position and search engine. I only want to do this for data that includes the URL type primary.

I have played around with an index/match combo formula but I couldn't get it to work.

Thank you for your help!

**MarvinP** · 06-29-2011, 10:32 PM

Hi taudano and welcome to the forum.
There are 2 very easy ways to do this problem.
1. Conditional Formatting and show top 5 and bottom 5 - see attached
2. Simply make the whole data set a TABLE and sort by Delta. Large to small show top 5 - and....
After sorting you can simply copy and paste the rows 2 to 6 from Data to Formulas.

The harder way to to create a counter column that tells which delta number and can pick the first 11 from the second to vlookup it over.
Another harder way to to do an Advanced filter and then sort it.

I'd stick with using the built in tools in Excel of Conditional Formatting or Table Sorts.

**Azam Ali** · 06-30-2011, 01:53 AM

Hi taudano

Soft file is enclosed for your reqired formula. Kindly note that it is array formual and executed thourgh Ctrl+Shift+Entr

Kindly note that similar formula is copy paste in the same highlighted cells.