Combining IF and OFFSET

**hurrell8510** · 09-13-2016, 07:26 AM

Hello all,

I've been struggling to find an answer to this problem, so thought I'd try on this site, as people seemed to be friendly and willing...

I wrote this formula, and it is doing what I want, but seems a little bulky for what I need it to do.

=OFFSET(INDEX(Table1[Quantity],MATCH(1,INDEX((Table1[Quantity]=LARGE(Table1[Quantity],ROWS(F$31:F31)))*(COUNTIF(F$31:F31,Table1[Quantity])=0),),0)),,-16)

In short, it looks up the highest value from a table, and then returns the information I want via OFFSET. When I drag down to duplicate, it give me the second highest, and so on and so forth. I only actually need it for the top 10 though.

What I wanted to do was to create two more formulas. One adds an IF function and the other adds an IFS function. They work on the same principle though. So I started, failed over and over until I decided to strip down the component pieces. When doing so I stumbled across this formula...

{=LARGE(IF(Table1[Tribe]="Gaul",Table1[Quantity],),1)}

This successfully gives me the highest number, that meets the condition of "Gaul". So, I want to then OFFSET that. The base component to move from D5 to C5 is...

=OFFSET(D5,,-1,,)

So in my mind all I have to do is place the LARGE array formula in the D5 and I should get the cell to the left of my highest quantity Gual.

=OFFSET({=LARGE(IF(Table1[Tribe]="Gaul",Table1[Quantity],),1)},,-1,,)

or

{=OFFSET(=LARGE(IF(Table1[Tribe]="Gaul",Table1[Quantity],),1),,-1,,)}

Both say that they see an error in my formula, I don't understand. I feel like the same root problem in my understand is affecting both the original formula and this one.

I hope I have given enough detail.
Regards,

**JohnTopley** · 09-13-2016, 10:59 AM

It would help if you posted a sample Excel file (not image) showing expected results.

To upload a file click "Go Advanced" then scroll down to "Manage Attachments"

**hurrell8510** · 09-13-2016, 01:33 PM

Thank you. Done.

To aid the upload a little the bit that I'm looking at is Table8[#All] which can be found in the tab Extraction in the cells B46:G55. The table pulls the information from the tab Index. I've got the row numbers of where I expect to find the data currently in the Player column, for you ease of reference.

Many thanks to anyone willing to take a look at this.

**JohnTopley** · 09-13-2016, 03:38 PM

Try

in C46

=INDEX(Table1[Player],SMALL(IF(Table1[Tribe]="Gaul",ROW(Table1[Rank])-ROW(Index!$A$3)+1,""),ROWS(Index!$A$3:A3)))

in D46

=INDEX(Table1[Alliance],SMALL(IF(Table1[Tribe]="Gaul",ROW(Table1[Rank])-ROW(Index!$A$3)+1,""),ROWS(Index!$A$3:A3)))

Enter both with Ctrl+Shift+Enter

**hurrell8510** · 09-13-2016, 07:00 PM

Ahhhh, that is so much cleaner. I can actually read and understand what's going on too. I've had quick play and applied your fix to the first extraction table too. I'm going to get rid of all the OFFSET formulas in there.

Many thanks for your help. I didn't expect anything so quickly!! I hope it didn't didn't take too much of your time.

**hurrell8510** · 09-14-2016, 12:25 AM

Actually this formula doesn't work when duplicating to other measurements or when using the dynamic nature of the first table. It only works in this given example.

Your code gives me the highest ranked Gaul listed in the table, not the highest by wheat. So when the table in changed, for example to Attack Strength, then the highest placed by Attack strength is listed in the wheat column, not the highest listed by wheat). When I make the new table (For Gauls by Attack Strength) it currently lists it in order of highest wheat, as the table was listed by wheat at that moment.

**JohnTopley** · 09-14-2016, 05:56 AM

If you want to rank by "Wheat" then use this format:

=INDEX(Table1[Player],MATCH("wheat",Table1[wheat],0)

where "wheat" is required cell reference

Copy down

**hurrell8510** · 09-14-2016, 07:00 AM

No I don't want to simply rank by wheat, although that formula looks much tidier than what I have.

In one table I want to rank by Wheat, out of the Gauls. Then in another table I want to rank Attack Strength, out of the Guals. Then in another table I want to rank Quantity, out of the Gauls.

Then, I'll do the same for Romans and Teutons

Then when I understand this, I will do another which will do it by servers. This will put two conditions on the data a "server" match and a "round" match.

**JohnTopley** · 09-14-2016, 08:16 AM

You already have the rankings of Gaul & Wheat, Gaul & Attack etc in Column F of your tables

in C46

=INDEX(Table1[Player],MATCH(F46,Table1[wheat],0)

will give you player matching "wheat" ranking in F46

in C59

=INDEX(Table1[Player],MATCH(F59,Table1[Attack Stength],0)

will give you player matching "Attack Strength" ranking in F59

**hurrell8510** · 09-14-2016, 09:19 AM

Ahhhh, ofc, I was being so thick. I was making it way too hard for myself. Thank you so much for your time and patience. I really appreciate it. You've saved me a lot of time and effort. If you enjoy a class of wine, I'd be more than happy to get a bottle or two reserved in a local store as a gesture of good will.

Regards,

Combining IF and OFFSET

LinkBack

Thread Tools

Rate This Thread

Display

Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Re: Combining IF and OFFSET

Thread Information

Users Browsing this Thread

Similar Threads

Need help in combining IF into OffSET

[SOLVED] Combining Offset or Index and Sum functions

Help with combining OFFSET and MAX Function

Combining offset, address, and match

Combining Vlookup and Offset

Combining vlookup with offset?

Bookmarks

Bookmarks

Posting Permissions