I am trying to use the Excel INDIRECT function to look up data in an array.

The top row is a group of alphabetical names

The extreme left column is a list of numbers (ranking). These "numbers" have been stored as Text, and they have been NAMED using the Excel naming function.

When using the INDIRECT function as an intersection:

{  =INDIRECT(Reference)_INDIRECT(Reference)  }

an error occurs---incorrect cell reference.

If I change the leftmost column to alphabetical names (First, second...etc) no problem. But this isn't practical for hundreds or thousands of rows of data.

  • Please provide an actual example to help us understand what you're seeing. – fixer1234 Jul 4 '19 at 0:40

Well... foremost among the problems here is that you cannot name a Named Range with numerals. Doesn't matter what is in the cells referred to, numerals formatted as text, or whatever, Excel will NOT accept a Named Range named, for example, 1. So there is a misunderstanding somewhere in what you are writing.

Next, the column names. Simply giving them alpha, or alphanumerical, labels will not give INDIRECT() anything it can use. Naming them, as Named Ranges would, but not simply filling them in as headers.

What WOULD work as you describe (without the INDIRECT() functions) is to have the columns as Named Ranges as well. When I say that though, I do not mean the cell A2 is named Bravo, I mean the DATA under it is, so if you have five rows of data, it would have the value "=Sheet1!$B$2:$B$6" and the Named Range A (the first row of data, in row 2) would have a similar value: "=Sheet1!$B$2:$F$2". Then the formula would simply be:

=A Bravo

and it would never even require implicit intersection.

Outside of saying Named Ranges like "1" and "33" exist, and wanting to use INDIRECT(), it seems this is what you are looking for.

I would suggest that the work was originally structured to use implicit intersection and that it is not possible in the way you want to improve things, but INDIRECT() still could not have contributed as described.

For example, say those Named Ranges named "1" or "33" did exist and referred to the cells the values were in. If INDIRECT() were to read one of them, it would look at the cell referred to, take its contents, and try to make a cell address out of them. So it would try to make "33" refer to a cell and it would fail. Hence the errors. If, instead, the Named Range "33" actually referred to a range of cells (the data to its right) (for instance: "=Sheet1!$B$34:$F$34"), INDIRECT() could read its contents and find that range which it could certainly convert to a cell reference and pass that on giving the same results as above, but in a rather more complicated, and unnescessary, way. But the name could not refer to the cell itself, but rather to the range of data as mentioned earlier.

If referring to the data, not to itself as implied, then "changing" the names to written out values like "First" would help because they would be real names Excel could use. But they could not ever have been "1" or "33" to change them from so...

To do what you wish, make Named Ranges for the columns and the rows, using legal names so it can actually be performed, and then use the formula given above.

As to the impracticality of creating those row names, don't go with "First" and so on. Just use the cell contents (the number present) and prepend an "a" to it so "a1" and "a2" and so on (Excel will not let one start with a numeral, period, so prepend). You can write a macro to do the physical work as it could use the cell contents to both give a name, then to alter the range specified for the data to its right, then move down. Loop it, or just copy and paste it as much as you feel needed to handle all the rows you could have. Be more sophisticated if you can. But even if it does all the labelled rows, then faults out, who cares? It did all the existing rows of data before it failed, eh?

Similarly the columns. Then, happy times!

| improve this answer | |

Roy: Thanks....you answer was enough of a clue that I was able to find the problem and the solution. Yes, Excel doesn't like using numbers as "Names". It also does not give an Error message if you try to Name a cell filled by a number (even if the number is stored as Text).

But Excel goes ahead and creates a Name for the cell, anyway. When it creates a name, it prefixes the number-value with an Underscore (_). So, the cell value 1000 becomes the name _1000.

The problem with using the INDIRECT function then becomes that the value stored in the cell and the name of the cell are different.

If you are then using a drop-down list to fill a cell, using a range of cells with values such as 1000, 2000, 3000..., the drop-down value in the cell will be 1000, whereas the NAME of the row or column you are trying to find by using the INDIRECT(1000) function does not exist. The INDIRECT function looks for {1000} but only sees {_1000, _2000, _3000, etc}.

And, that lack-of-correspondence then generate the #REF error message.

So, the "moral of the story is", if you want to use numerical values to point to a row or a column (via a drop-down list, for example), you must prefix each of those numbers in the source array with an underscore (_)---when Excel is told to create names for these, the names will be the same as the value stored in that cell (and displayed in the drop-down). Hope this makes sense.Thanks for your help!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.