Variable column reference (formula compression)

[Median]

Hi guys,

Not quite sure where to start with this one. Basically, I need the bits of the formula in I6 - to begin at different points dependant on the value of G6. Might not have been clear there!!

Currently, the formula in I6 uses C6 (column 1) as its reference:

0SexLSS5QYK82RefLf6Jsg.png

However, I need to to use the value at G6 as the reference (i.e. formula begins at column 3). Therefore, certain parts of the formula get compressed:

BiAn3a5_RHSOhHJaowrBvg.png

Is this possible?

Thanks
Mdn

[63falcondude]

If you update your sample workbook with the desired results (manually entered), instead of the non-working formulas, I'll take a look.

[Median]

Really appreciated Falcon

Here's the updated workbook (perfect result is in column I)

[leelnich]

Are you trying to use the "Last 4" values or something like that?

[Median]

Hi Lee

Essentially, these two parts of the formula, change depending on the value of G7:

HgC5AsV_QOOQE_nh4hU1mw.png

[63falcondude]

Try this in I6:

=IFERROR(IF(AND(INDEX(C6:F6,G6)<H6,SUMPRODUCT((C$5:F$5>=G6+1)*(C6:F6>=H6))),IF(F6>H6,"WINNER","LOSER"),""),"")

[Median]

Fab-u-lous Falcon!

Massively appreciated

[63falcondude]

You're welcome. Thanks for the rep!