0

I'm stuck with finding the max value in a column based on a criteria that I know only a part of it.

Let me explain better, here is my table

enter image description here

What I would like to do is:

1) Find the max number in Column A based on my 1st criteria that is "1".
2) If I have the criteria "1" I would like to find the max number that begins with "1", so in our example the max number would be "10010".

Some tips of how I would obtain this? If you prefer VBA is a valid option also.

Thank you

ChangeWorld
  • 421
  • 1
  • 6
  • 22

1 Answers1

3

One option, something like the following:

=MAX(IF(LEFT(A2:A8,1)="1",A2:A8))

enter image description here

Array formula, so depending on your version of Excel you may need to confirm with Ctrl+Shift+Enter.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Wow! Why it did not consider "20000" also? It's greater than "10010" – ChangeWorld Jan 27 '20 at 14:23
  • 1
    But `Left(20000,1)` doesn't equal 1. The first part of the formula effectively replaces 20000 with 0, well actually FALSE. You can use *Formulas* > *Evaluate Formula* to see what the formula is doing. – BigBen Jan 27 '20 at 14:24
  • Hey @BigBen I'm strugling to write this formula in VBA excel where the number `="1"` is a variable. Could you help me out? – ChangeWorld Jan 28 '20 at 13:26
  • Sure. Maybe ask as a new question and ping me here? – BigBen Jan 28 '20 at 13:27
  • here you go https://stackoverflow.com/questions/59949930/write-properly-a-formula-in-vba-excel – ChangeWorld Jan 28 '20 at 13:32