Problem with HLookup - Sometimes works, sometimes blank.

[Regnab]

Afternoon All,

Due to concerns that the ladies of this world will find me less
attractive if I pull out my remaining hair, I shall pop this one up and
see if anyone has got any ideas...

I'm trying to use HLookup to lookup the prices of products in a table
to the left of where I am doing my calculations. Going down the page
was the dates, and each product could have a different price on each
date. The idea was that the user would type in the product code and the
formula would perform the calculations including the price. Originally,
I used HLOOKUP as part of the formula but it was inconcistent. It would
work for a while, and then randomly (usually when I changed the value
in another cell - an unrelated one), it would go blank. I would then
have to go and delete the formula and then press undo and hey presto,
it was back to working.

The rather messy solution I came up with was to put the HLOOKUP in
another column and then reference that cell (and hide the cell when the
user was involved).

Just wonding if anyone else had had anything similiar or if people have
any ideas,

Cheers

Reg

 

[Guest]

.. Originally,
I used HLOOKUP as part of the formula but it was inconsistent. It would
work for a while, and then randomly (usually when I changed the value
in another cell - an unrelated one), it would go blank. I would then
have to go and delete the formula and then press undo and hey presto,
it was back to working.

Just a thought on the part above ..
is your book calc mode set to automatic ?
(or it may be inadvertently set to manual calc mode)

To quickly check / change calc mode:
Click Tools > Options > Calculation tab
Check Automatic > OK

---

 

[Regnab]

Yeah - it's set to Automatic. All the other formula's are updating etc
as per usual...

 

[Guest]

Yeah - it's set to Automatic. All the other formula's are updating etc
as per usual...

Welcome back! Other than checking the data consistency (between the lookup
values and the top row reference values in the hlookup's table array, eg:
check whether it's a case of real numbers being looked up/compared with text
numbers - or the other way around; check for any extra white spaces if its
text strings being compared - TRIM can be used to make the matching more
robust), I don't have further ideas on your subject line. Hang around awhile
for insights from others.

Btw, did the suggested incrementer for the hlookup's row_index_num work for
you in my response to your other post (Re: HLOOKUP - Adjusting the lookup
value...) ?
Perhaps you could throw a feedback line over there?

---