Cross Sheet Match Returning Incorrect Value

Matt Babcock

I'm making a project template that feeds a portfolio page and to make the portfolio page easy to update I set a goal to only use a single cross sheet reference between projects. Overall it's working well but I'm stuck on one part. If my users move the columns around they mess up my Index functions. My idea was make a row that had all of the column names in it and then to do a match on that row to determine the column. But when I do that my formula isn't returning the correct column number.

=MATCH("Status", INDEX({Template - All}, 2), 1) returns a value of 8 which is obviously wrong. And other search values return what seem to be a random column numbers or no match.

Any help is appreciated!

David Joyeuse

Hi Matt,

The MATCH function returns a line number.

INDEX returns a value within a range depending on the line number.

So in your formula you're trying to return the highest line number of "Status" in a range that consist of the value that is stored on line 2 of your "Template - All" range. (And Line 2 doesn't refer to the line 2 in your sheet, but the 2nd line of the range in case the range doesn't start on line 1).

Your problem here is not really on the formula, but on the fact that moving around columns is messing your index formulas which shouldn't happen. If Index/Match are correctly set up, users can move columns around without messing with them. Maybe we should start working on those formulas first.

Matt Babcock

My columns are referenced by hardcoded numbers in my index formulas. this was my attempt to move away from that and make them flexible.

My range is the entire sheet so in this case the 2 is a consistent reference.

I didn't mean to send the version with the 1 set for match type, with it set to 0 I still receive results that don't make sense like incorrect column numbers. When I came back to my sheet that same code is now returning a 5 instead of an 8, neither of which make sense to me.

Are you saying I can't use match to return a column number of a value if I feed it a row instead of a column?

David Joyeuse

Usually Match returns a value that is a line. Not a column.

Problem from what I read above is that your range is an entire sheet. That doesn't goes along well with INDEX/MATCH as MATCH treats the whole range as a single column.

Make sure your range are one column only, or a part of it like [Column Title]1:[Column Title]10, or [Column Title]1:[Column Title]@row. Thus you'd get rid of people moving columns around and messing with your formulas.

Now, I've never tried it, but your formula might work this way:

=MATCH("Status",{Template - Line 2};0)

If your range is equal to just Line 2 of your template, it should give you back the column value.

Matt Babcock

That's why I was nesting an index inside my match, so that it would only send 1 row of the range to the match function. Shouldn't Index({Template - All},2) only send a single row of the range?

And why is it returning any other value than the correct one or no match?

I totally get making multiple references per sheet but that make adding projects very cumbersome. Other than the issue of users reordering columns I can currently add new projects by copying a template row, updating the template row's reference and then pasting the template row with the old reference back in.

Matt Babcock

Just bumping this back up, can anyone explain why this approach isn't working?

Paul Newcome

The INDEX function only pulls a single cell. If you need to match on a specific row and column within a range, you are going to need to have a row in your source sheet that contains a unique data point for each column in the very first row (I usually just replicate column names), and then you can run a second MATCH function across that to pull your column number.

=INDEX({Full Sheet Range}, MATCH(row number match), MATCH(column number match))