Ask Your Question
0

Problem with hlookup and indirect [closed]

asked 2019-08-13 23:28:07 +0200

gairsty gravatar image

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?

C:\fakepath\working_vlookup.ods

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-08-16 17:25:12.084694

Comments

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-14 08:52:20 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-08-14 13:54:27 +0200 )edit

Ah - thanks; I didn't look there. :-) thank you for explanation!

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-14 15:23:02 +0200 )edit

Hello @gairsty, as he made this appointment, does not appear in the places I know.

image description

image description

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 2019-08-14 17:57:36 +0200 )edit

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.

image description

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 2019-08-14 18:03:22 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-08-14 14:15:10 +0200

erAck gravatar image

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.

edit flag offensive delete link more

Comments

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.

m.a.riosv gravatar imagem.a.riosv ( 2019-08-14 16:35:30 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-08-14 17:24:20 +0200 )edit

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!

gairsty gravatar imagegairsty ( 2019-08-14 20:37:53 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-08-13 23:28:07 +0200

Seen: 22 times

Last updated: Aug 14