Index Match only returning first index option?

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:

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

What I've tried:

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

Find more posts tagged with

Comments

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. 👍️

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.