Vlookup/Index/Match/Offset

[adnuta1]

Hello All, 1st Post!!



I Have a data set with multiple tables that are each formatted for dates, a name and a count function, all pulled from access. Below is an example of a portion of one table.

5/22/2015	HP	3
5/22/2015	Blue Line Drilling	2
5/22/2015	TDG.cn	1
5/22/2015	Ruff Drilling	1
5/22/2015	MCG Drilling & Completing	1
5/22/2015	J-W Energy Company	1
5/22/2015	Felderhoff Brothers Drilling	1
5/22/2015	Double K Drilling	1
5/15/2015	TDG.cn	1
5/15/2015	Ruff Drilling	1
5/15/2015	MCG Drilling & Completing	1
5/15/2015	J-W Energy Company	1

<tbody>
</tbody>

<tbody>
</tbody>

In a separate tab I'd like to create a table that pulls the largest count number for a specific date (cell reference) and then in a separate column the corresponding name and then repeating for the 2nd largest value and so on. Could anyone please advise? I am trying to avoid adding helper columns that ranks the count. Please let me know if additional details are needed.

 

[LloydFinancials]

Hello Adnuta1!

You can try this, assuming the dates are sorted.

Excel 2010
	A	B	C	D	E	F	G
1	5/22/2015	HP	3			5/22/2015	3
2	5/22/2015	Blue Line Drilling	2			5/15/2015	2
3	5/22/2015	TDG.cn	1
4	5/22/2015	Ruff Drilling	1
5	5/22/2015	MCG Drilling & Completing	1
6	5/22/2015	J-W Energy Company	1
7	5/22/2015	Felderhoff Brothers Drilling	1
8	5/22/2015	Double K Drilling	1
9	5/15/2015	TDG.cn	1
10	5/15/2015	Ruff Drilling	1
11	5/15/2015	MCG Drilling & Completing	1
12	5/15/2015	J-W Energy Company	2
Sheet3

Cell Formulas
Range	Formula
G1	=LARGE(OFFSET(INDIRECT(ADDRESS(MATCH(F1,$A$1:$A$12,0),3)),,,COUNT(F1,$A$1:$A$12),),1)

Luke