Index Match only returning first index option?

Gen Ireland

Hi,

The Goal:

Use index match to return a column name based on which column in a given range has the maximum value of that range.

The problem:

I can only get 1/5 of my return options to work, and only when I add 1 as 'column index' field to the Index formula (usually column index is optional- I've never needed it in any other SS or Excel index match)

Addressing the Obvious:

1. All column types are text/number
2. Match/Search value is formula driven; known EXACT match in search-range (aka no keying errors)
3. Formula is drag-copied down (aka no keying/syntax errors)

What I've tried:

1. manually adjusting the column index to 2+ does not change/fix the return
2. manually removing column index on erroring lines does not change/fix the return (remains "#INVALID VALUE" in all cases)
3. adjusting the remaining return options to alpha-only (i.e. "second option" instead of "1-30BT") does not change fix/the return
4. removing column index returns "#INVALID COLUMN VALUE" on previously working rows

Paul Newcome

The MATCH function is producing a number that reflects where the match was located within the given range. The problem with this is that the MATCH function works from left to right and from top to bottom.

1.....2.....3.....4

5.....6.....7.....8

9....10...11...12

The reason you need to enter a column index in this case is because your INDEX function is pulling from a single row across multiple columns. Therefore the row index would be 1 and the column index would be the changing number.

I know I have done this before, but I can't remember the solution right off. Let me dig through my notes and see what I can find. I'll get back to you.

Paul Newcome

Taking another look at what you are trying to do, I realized I was over thinking it.

Use a 1 for the row index and just move the MATCH function into the column index portion.

=INDEX(range_to_pull_from, 1, MATCH(................))

Gen Ireland

Thank you Paul and Lewis!

You were both right- the horizontal orientation of the index options was the issue. I was led astray by the original excel, in which the formula worked with horizontal index orientation//no column index.

I re-oriented my returns vertically and the formula worked for all cases without any column index. I then moved the index to a helper-lookup/reference sheet I had already made for a separate VLOOKUP in another column of this sheet. I was also able to remove the "Max" helper column to further tidy up the formula.

the final formula is:

=INDEX({Match Returns}, MATCH(MAX([Within Terms]1:[181+ BT]1), [Within Terms]1:[181+ BT]1, 0))

Thanks for your time on this!!! I really appreciate both your help :)

Paul Newcome

Happy to help. 👍️