I have a VLOOKUP formula, [ =VLOOKUP(G2,INDIRECT(F2),2,0) ], where F2 varies, but is a named range. This formula works.
When I try the same with HLOOKUP, [ =HLOOKUP(G17,INDIRECT(F17),2,0) ] the INDIRECT fails. If I put the name of the Range directly into the formula, it works, however if I try to do it where the name of the Range is in F17, failure…
I think this is a bug, has anyone else come across this?
I’m really surprised that VLOOKUP works in this case at all; and I’d consider that a bug. In your case, what you call “ranges” should possibly work as auto-assigned column/row names (named by their first heading cell, and consisting of contiguous filled cells) - so (1) the “range” should only consist of one column/row - and thus using index 2 in VLOOKUP/HLOOKUP should be illegal; (2) the range should have the characters in its 1st (the only) row/column, so finding a number in it should never happen; and (3) the heading cell itself should be excluded from the range, so finding it should never happen…
And also I emphasized possibly in “should possibly work”, because you used single-character names, which conflict with standard column names…
what you call “ranges” should possibly work as auto-assigned column/row names
No, in this case those names are defined as database ranges and more than one column wide.
Ah - thanks; I didn’t look there. 
thank you for explanation!
I think the problem is related, by the way you named it, I renamed it as I know it and it’s working. In “F17”, use F17.
First, the formula in F18 is
=HLOOKUP(G17;INDIRECT("F17");2;0)
which INDIRECT() produces the address of the literal string F17 and not the content of F17, and thus the formula can’t work at all in this context, hence Err:502.
Now if that is changed to
=HLOOKUP(G17;INDIRECT(F17);2;0)
instead, it still results in error because the m database range includes column labels so the actual data range is A13:G13 and not B12:G13 as intended, there simply is no 2nd row, hence Err:502.
Redefine the database range to not contain column labels, but note that the first column will be included as data as there is no Contains row labels because database ranges’ fields are supposed to be column vectors.
This of course works in the VLOOKUP() context because it does a vertical lookup.
Thanks for the clarification, some idea cross my mind when thinking about the issue. But works with =HLOOKUP(G17;m;2;0) and theoretically should not.
INDIRECT() treats a database range differently than specifying it direct. Reason is that Excel does it the way INDIRECT() does (for so-called table names, which basically are database ranges), capping column header and total rows (source code has such comment). I’m not sure what it does when a table name / database range is given directly.
I had also tried the (F17) rather than (“F17”).
The problem was in how I was naming ranges. I had been using Alt+D+D (Data > Define Range…) I was unaware of the little drop-down menu for "Manage Names…
This, I believe has fixed my problem, and has most certainly provided me with the correct method of achieving what I want.
Thank you one and all!