Find Row of Large Value in Array & Return Values in Different Named Range

[jon7187]

Hi Guys, long time lurker 3rd time poster.

I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:

{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.

My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?


Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.

Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.

Thanks in advance and below is a link to an example document to clarify this.

http://jonlorenzini.com/wp-content/uploads/2012/02/MrExcelQuestion.xlsx

-Jon Lorenzini

 

[circledchicken]

Hi and welcome,

Here's an example for your first table, the other one would be similar:

Excel Workbook
	I	J	K
2		Drop Down
3	Segment:	fruit
4
5	Sorted By Impression
6	Keyword	Impressions	Clicks
7	orange	279	164
8	banana	263	48
9	apple	21	1
Example

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Why were you asked not to use PivotTables? I think they are a very good, flexible solution as shown in your workbook.

 

[jon7187]

Thanks for the reply circled chicken. I was asked cause one of the higher ups had issues in the past with pivot tables breaking on him so I was asked not to use them (plus I welcome a challenge but this one proved to best me).

These formulas worked for unique values but in the event the two keywords have the same number of impressions:

ie, changing dog from 88 to 181, the returned values are now:

dog, 181, 6 and
dog, 181, 6

instead of

elephant 181, 6 and
dog 181 16


Thanks,
Jon

 

[jon7187]

To add - I think the possibility for duplicate values is why a row function would work out better. Thanks again for your reply!

 

[circledchicken]

Ah, I see what you mean. Do you only have 3 your looking or is that just for the example? If so, maybe try:

Excel Workbook
	I
6	Keyword
7	elephant
Example

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jon7187]

There is top 20 keywords but I can always build out a really long one haha

thanks again for the continued help.

 

[circledchicken]

The following should work for as many as necessary rather than doing a much longer version of the earlier formula:

Excel Workbook
	I
6	Keyword
7	elephant
Example

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[circledchicken]

Actually that can be shortened to just:

Excel Workbook
	I
6	Keyword
7	elephant
Example

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jon7187]

Worked like a charm. Thanks for your help! If you are ever in NYC I'll buy you a beer.

I'll probably throw a post up on this for other search engine marketers to use. Do you have a site I should link to for credit?

 

[circledchicken]

Haha your welcome - nice picture!

I just posted a shortened version of that, I realised some of the earlier formula was unnecessary.

If your posting a link maybe just do one to MrExcel.com - helped me loads too!