Lookup In "Split" Ranges As In One

I have a situation. I need to perform a HLOOKUP and, let's say, find a Top 5 largest values in a row. The issue is that my data is structured something like this:
A B C D E F G H I J K L
# # # S # # # S # # # S ...etc

Each # is a numeric value, and S are their sums. Needless to say that I don't want to do a lookup in the whole A:L range, since sums will show up as top values. So what I want to do is to perform a lookup ignoring the sums, meaning in ranges: A:C,E:G,I:K. So it's like making a new "unified" range out of several small pieces. But this should be done within one formula, of course.

Is anything like this possible by any chance, guys/gals?
Thanks so much in advance!

Hi Splendidus,

Assuming the data is in A1:L1 (with sums in D1, H1 and L1), try this array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER):

=LARGE(IF(MOD(COLUMN($A$1:$L$1),4)<>0,$A$1:$L$1),ROW(A1))

Fill that down five rows to get the first through fifth larges values. (The "ROW(A1)" bit will update to A2, A3, A4 and A5, returning 1 through 5, respectively.)

Originally Posted by Paul

Hi Splendidus,

Assuming the data is in A1:L1 (with sums in D1, H1 and L1), try this array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER):

=LARGE(IF(MOD(COLUMN($A$1:$L$1),4)<>0,$A$1:$L$1),ROW(A1))

Fill that down five rows to get the first through fifth larges values. (The "ROW(A1)" bit will update to A2, A3, A4 and A5, returning 1 through 5, respectively.)

Thanks so much, Paul! The thing however is that total columns are not always every Nth column. Here's a sample file attached to illustrate my point. I'd very much appreciate any help on this!

Arrayformula:

=LARGE(IF(COLUMN($A$3:$I$3)=4,"",IF(COLUMN($A$3:$I$3)=9,"",$A$3:$I$3)),ROW(A1))

Originally Posted by HSV

Arrayformula:

=LARGE(IF(COLUMN($A$3:$I$3)=4,"",IF(COLUMN($A$3:$I$3)=9,"",$A$3:$I$3)),ROW(A1))

Thanks so much for your reply! It does help, but apparently I need a tad more functionality than that. I did get the formula to work in my case, but it's just I need a bit more of a twist: I thought I could get the necessary effect once I get the formula you recommended to work, but I can't.
So, to illustrate the issue better, have a look at the attached file.

The functionality needed is the following:
1. User inputs a code
2. Based on that code a vertical lookup is done
3. After that in that found row (step above) a horizontal lookup is done, looking for largest X value. This must EXCLUDE the total columns
Let's say this is formula 1. Formula 2 is the same, but it need to return not largest X value, but a NAME of a largest X value. It also must exclude total columns as well needless to say.

Is anything like this possible to do within the same formula? Or two, actually.
Thanks so very much in advance!

Something like this ?

Originally Posted by HSV

Something like this ?

Not "something" like this, it's EXACTLY like this!!! :D

But there's one "but" (as usual I guess). Here's the formula I adopted from yours (I put the value lookup formula INTO the product name lookup formula - merged them together):

=INDEX(DataTable!$DH$46:$IT$46,MATCH(LARGE(IF(OR(DataTable!$DH$46:$IT$46<>"TOTAL",DataTable!$DH$46:$IT$46<>"sTOTAL",DataTable!$DH$46:$IT$46<>"cTOTAL",DataTable!$DH$46:$IT$46<>"gTOTAL"),INDIRECT("DH"&MATCH($D$3,CodesColumn,0)&":"&"IT"&MATCH($D$3,CodesColumn,0)),""),ROW(DataTable!A46)),INDIRECT("DH"&MATCH($D$3,CodesColumn,0)&":"&"IT"&MATCH($D$3,CodesColumn,0)),0))

DataTable - name of the table where all data is
$DH$46:$IT$46 - headings line (under them data starts from row 47 onwards)
$D$3 - cell where code value being looked up is stored (code is a number from 1 to 1000)
CodesColumn - named range where all codes are stored on DataTable sheet (range heading is A46, data starts at A47)

For some reason this part "INDIRECT("DH"&MATCH($D$3,CodesColumn,0)&" returns a WRONG row number. For instance, let's say we're looking for a match for code 86. For some strange reason result returned for this is 76 (which then merged with DH giving DH76). Meaning after a lookup for 86, it returns that code 86 is in 76th row, and I honestly can't figure out why. As far as I understand the formula is looking for a row where code 86 is located, if so, it should return 122 (row 122), not 76.

Any ideas on how this might be solved? I'll play with LOOKUP in the meanwhile
In any case, thank you so very much for your kind help and time!!

Maybe you could upload your file.

Originally Posted by HSV

Maybe you could upload your file.

If I could, I most definitely would. I'll try to come up with an exactly-matching sample

Hi

Hope this what you looking for.

Please click on the attachment.

Originally Posted by HSV

Maybe you could upload your file.

Here's a sample, hope it's clearer now.
The data on the Data sheet is in the EXACT format I must have it in, meaning there's little wiggle room when it comes to changing it.
Also, putting a rank above would also not work as far as I understand, since I need a result returned for each single code (=formula should process rows where a code was looked up).

I mean, the formula you originally suggested gave me exactly what I need, but for some reason it just doesn't work on the data structure that I have. Or maybe I'm just missing something here :/

In any case, I'd much appreciate any help!

this array no helper needed
=INDEX($A$2:$P$2,MATCH(LARGE(INDEX($A$2:$P$2<>"total",0)*INDEX($A$3:$P$3,0),ROW(A1)),INDEX($A$2:$P$2<>"total",0)*INDEX($A$3:$P$3,0),0))
or with a helper see attached

Splendidus,