INDEX/MATCH error "#N/A" when I have some merged rows, to use RANK as an end goal

[dmorganoh]

Hello,
I have gotten the INDEX/MATCH formula to work just fine when it is all neat and orderly. But I am trying to neatly RANK my investment clients, but because my manually-entered values in the spreadsheet above where I want to view my RANK Table, I have several column/rows that are merged. This seems to be giving me a problem, but I'm not sure exactly what the problem is.
How can I better describe the issue to have someone help me?
Much thanks,
DM

 

[manc]

Hi DM,

Not saying it's not possible, but I've found that having merged cells when using INDEX/MATCH and array formulas just creates problem after problem.
The easiest solution would be to unmerge the cells and think of another way of displaying that info.
It should work then, right?

Best regards
manc

 

[Joe4]

Welcome to the Board!

Merged cells are a known nuisance, and should try to be avoided whenever possible. That cause problems with a lot of different things.
For cells merged across a single row, the "Center Across Selection" gives you the same visual effect without all the issues.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[dmorganoh]

I'll try that and let you know.
Thanks!
DM

 

[dmorganoh]

Mine are merged in columns. Is a fix similar you think?
DM

 

[Joe4]

Mine are merged in columns. Is a fix similar you think?

So, you mean that you have multiple rows merged within a single column?
Unfortunately, I have not seen an equivalent to the "Center Across Selection" for this.

Are you sure it isn't something that cannot be rectified by changing the row height instead of merging the rows?

 

[dmorganoh]

I unmerged every row and column. Nothing. The formula looks as clean and pure as a simple 10 row sample I set up that worked perfectly.
Could it be that there is some hidden, embedded formula within the spreadsheet that causes it?
At this point, I have no idea. I've got 10 hours into this one problem. Just can't seem to find any fix.
DM

 

[Joe4]

Could it be that there is some hidden, embedded formula within the spreadsheet that causes it?

I doubt that would be the problem, unless it is somehow related to your data and there are issues with those formulas.
Without seeing your data and formulas, it is very impossible to say what the problem may be. We would need details to help you any further.

 

[dmorganoh]

Is there any way to post the file worksheet? I created a fake one that is giving me the same problem.

 

[Joe4]

You can upload the file to a file sharing site (i.e. Google Docs, DropBox, etc) and post a link here.
Just bear in mind that due to security concerns, many people may be unable or unwilling to download it.
I cannot download it from my work location (where I currently am), but can from my home computer.

There are also tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.