Complex Lookup, Index and Match formuale

Gaurav Chauhan
Gaurav Chauhan ✭✭✭✭✭✭

Hi Team,

I am trying to build these formuale to fetch the values on another sheet tied to a month based on an Online video. Would you be able to help me in here.

Have created a the Online form to pick the Client Name/ Month. However, the IF MAX formulae doesnt come out right and also the created date doesn't show my minute and seconds.


  • James Keuning
    James Keuning ✭✭✭✭✭

    Can you put just the MATCH part of your formula in a cell and see what it is returning? I agree that the formulas in the first and second screen shots are not in sync.

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Hi James, would it be possible to spend 30 mins over call to review and fix this.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Gaurav Chauhan

    It looks like you may have some extra parentheses in here - it shouldn't make too much of a difference but I would take them out just in case.

    I would also suggest using a different value in your first formula instead of 1, just to check if this helps the MATCH find the right cell.


    =IF(Created@row = MAX(Created:Created), "Max", "")

    ^ notice that I don't have (these) around the MAX, it's not needed.

    Then in your INDEX(MATCH, look for the word "Max" instead of a number:

    =INDEX({Month no}, MATCH("Max", {Latest No}, 0))

    Again, you don't need the extra (these) around the formula.

    If this doesn't work, try adjusting the 0 at the end of the MATCH - instead of setting it to "unsorted", use "sorted descending" which would be -1

    =INDEX({Month no}, MATCH("Max", {Latest No}, -1))

    See: MATCH Function