Complex Lookup, Index and Match formuale

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

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

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

Genevieve P.

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.

Try:

=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

Cheers,

Genevieve