Another Excel Question

[oxford drinker]

If I may, another Excel question.

I have used the MAX command with some criteria to find the highest value in a column (Column L) from a separate sheet in the same workbook. I now want Excel to find that cell in which the maximum value occurs and tell me the value of column B in the same row.

Is that possible?

TIA

[FunkyGibbon]

check out the Offset function - that should do what you need

https://support.microsoft.com/en-us/office/offset-...

[Mr Penguin]

Or index / match although it only returns one of the max values, if you have more than one.

[fbc]

FunkyGibbon said:

check out the Offset function - that should do what you need

https://support.microsoft.com/en-us/office/offset-...

Mr Penguin said:

Or index / match although it only returns one of the max values, if you have more than one.

You'd actually use both of these. Use MATCH to return the row in Column L on which your MAX value occurs. And then use OFFSET to return the value from Column B on that row.

Edited by fbc on Tuesday 30th August 15:37

[RizzoTheRat]

=MATCH(MAX(L:L),L:L,0)

will tell you the row number the maximum value in column L

Edited by RizzoTheRat on Tuesday 30th August 15:38

[oxford drinker]

Thanks for the replies and the speed of reply! I will have a go with the suggestions. Cheers!

[FunkyGibbon]

just had a go - may not be the most elegant:

Values in column L - data to return in column B

=INDIRECT(ADDRESS(MATCH(MAX(L:L), L:L, 0), 2))

[oxford drinker]

This has stumped me..... appropriate as the spreadsheet is cricket stats. Forgive me but I'm not getting anything to work.

My "Partnerships" spreadsheet records all the data on partnerships for each wicket in different types of match. On a separate tab in the workbook I have found and displayed the highest partnership (score) for the first wicket in T20 matches

=MAXIFS(Partnerships!$L$2:$L$18000,Partnerships!$F$2:$F$18000,"T20",Partnerships!$K$2:$K$18000,"1")

This correctly returns a value of 86 which happens to be in L3224 in the Partnerships spreadsheet. I now want on the second tab (ie not in the Partnerships sheet) for it to tell me that this value is in cell L3224, and then to display the value of column B in the same row, ie B3224.

Apologies if any of you have already told me this, but I've reached the limit of my Excel ability.

[FunkyGibbon]

=INDIRECT("Partnerships!" & ADDRESS(MATCH(MAX(Partnerships!L:L), Partnerships!L:L, 0), 2))

Will get you the value in Partnerships tab column B

If your data is in multiple columns just swap out the MAX(Partnerships!L:L), Partnerships!L:L, 0)

with your formula

e.g.

=INDIRECT("Partnerships!" & ADDRESS(MATCH(MAXIFS(Partnerships!$L$2:$L$18000,Partnerships!$F$2:$F$18000,"T20",Partnerships!$K$2:$K$18000,"1"), 2))

should return column B

=MAXIFS(Partnerships!$L$2:$L$18000,Partnerships!$F$2:$F$18000,"T20",Partnerships!$K$2:$K$18000,"1"),

will return the highest partnership

Edited by FunkyGibbon on Tuesday 30th August 16:42

[oxford drinker]

Thank you Funky Gibbon! Still trying to fathom how it works, but it does. I owe you a few virtual beers!

[FunkyGibbon]

oxford drinker said:

Thank you Funky Gibbon! Still trying to fathom how it works, but it does. I owe you a few virtual beers!

Glad to help

It works thus:

You have your MAX or MAXIFS function finds the highest partnership

MATCH finds the row of the above

ADDRESS creates an absolute address for row the comma 2 tells it to use $B (3, would be $C, 4 $D etc).

INDIRECT gets the value of the cell at the absolute address

to make it all work across sheets append Partnerships! to all cell references in the formulas

HTH

Edited by FunkyGibbon on Tuesday 30th August 17:04

[oxford drinker]

Actually it's not always picking up the right value, if there are two 86s in the list it picks up the first it finds even though only one has the correct criteria (wicket 1 in T20). Back to the drawing board!

[FunkyGibbon]

oxford drinker said:

Actually it's not always picking up the right value, if there are two 86s in the list it picks up the first it finds even though only one has the correct criteria (wicket 1 in T20). Back to the drawing board!

Try taking the quotes of this:

Partnerships!$K$2:$K$18000,"1"

to become

Partnerships!$K$2:$K$18000,1

Assuming the value of 1 is stored as a number

Edit to add to ignore the above - it is the match function not working as expected not the MAXIFS....

Edited by FunkyGibbon on Tuesday 30th August 17:25

[FunkyGibbon]

Fixed it:

=INDEX(Partnerships!B:B, MATCH(1,(Partnerships!L:L=MAX(Partnerships!L:L))*(Partnerships!F:F="T20")*(Partnerships!K:K=1),0))

on your other tab

This is an array formula, so when you paste it in your cell you need to use shift+control+enter and not just enter it will gain curly brackets (Unless you have excel 365 in which case enter will work)

{=INDEX(Partnerships!B:B, MATCH(1,(Partnerships!L:L=MAX(Partnerships!L:L))*(Partnerships!F:F="T20")*(Partnerships!K:K=1),0))}

should return column B when type is T20 and wickets = 1

=MAXIFS(Partnerships!$L$2:$L$18000,Partnerships!$F$2:$F$18000,"T20",Partnerships!$K$2:$K$18000,"1"),

will return the highest partnership value

now you could have the values for type of cricket and wicket count as drop down lists and reference them in the above formula

Good luck

Edited by FunkyGibbon on Tuesday 30th August 18:25

[oxford drinker]

Thanks very much for your help. Seems my Excel skills have been exceeded....

[FunkyGibbon]

If you are having problems with this, PM me a sample of your spreadsheet and I'll have a go at sorting if you wish.

[paulrockliffe]

Put it in Power Query. Your data is row-independent, so you've nothing to lose other than these horrific formulas!

[Vanden Crash]

paulrockliffe said:

Put it in Power Query. Your data is row-independent, so you've nothing to lose other than these horrific formulas!

I was about to say the same.

Power query is the best way to do anything (other than losing the excellent xlookup)

Power query is very easy to learn as most of the DAX formulas are very similar but importantly power query is proper data management whereas excel has always been a posh ledger that’s developed over time.

If anyone would like some tips on using excel power query just ask

[paulrockliffe]

Then when you've done it in Power Query, bear in mind you can export the M code (PQ isn't DAX) and import it into Power BI Desktop and have your mind blown....

[oxford drinker]

FunkyGibbon said:

If you are having problems with this, PM me a sample of your spreadsheet and I'll have a go at sorting if you wish.

Thanks, have sent mail!