HLOOKUP easy, but wrong if no exact match?

[boznian]

Actually 2 issues, but here is the most pressing:

=HLOOKUP(E3,$AV$5:$CC$7,3,TRUE)
...works fine as long as the value spec'd in (E3) exists in the table. If not, then I get some (seemingly) random value, which is unacceptable.

So I tried this:
=IF(H3,(HLOOKUP(E3,$AV$5:$CC$7,3,TRUE)),"")

...works fine in my test sheet but I get #VALUE! in my live project?? The "IF(H3" is referring to a text value cell (not the result of any formula).

So A) any thoughts on the #VALUE! error
and B) is there a better (preferred) way to do this rather than HLOOKUP

thankssssss

 

[Richard Schollar]

Do you only want to match against an exact match? If so, then you should be using FALSE as the fourth argument to the Hlookup:

=HLOOKUP(E3,$AV$5:$CC$7,3,FALSE)

If no match is found, this returns #N/A. If you want to return an empty string instead then in xl2007 or above:

=IFERROR(HLOOKUP(E3,$AV$5:$CC$7,3,FALSE),"")

if 2003 or below:

=IF(ISNA(HLOOKUP(E3,$AV$5:$CC$7,3,FALSE)),"",HLOOKUP(E3,$AV$5:$CC$7,3,FALSE))

 

[prabby25101981]

You should use "TRUE" as range_lookup (last bit of the hlookup) only if the table where you are trying to find a value is sorted. This is the only way HLOOKUP will produce right results.

Have you tried sorting the table and then use HLOOKUP?

 

[boznian]

"if 2003 or below:

=IF(ISNA(HLOOKUP(E3,$AV$5:$CC$7,3,FALSE)),"",HLOOKUP(E3,$AV$5:$CC$7,3,FALSE))
"
.. OK, that works great! Seems complicated for what I thought should be pretty easy. Never heard of ISNA or any of the other 8 "IS" functions until now! Learning, learning...

"Have you tried sorting the table and then use HLOOKUP? " - yes, the table was already sorted.

Thanks a bunch!

 

[Aladin Akyurek]

"if 2003 or below:

=IF(ISNA(HLOOKUP(E3,$AV$5:$CC$7,3,FALSE)),"",HLOOKUP(E3,$AV$5:$CC$7,3,FALSE))
"
.. OK, that works great! Seems complicated for what I thought should be pretty easy. Never heard of ISNA or any of the other 8 "IS" functions until now! Learning, learning...

"Have you tried sorting the table and then use HLOOKUP? " - yes, the table was already sorted.

Thanks a bunch!

If AV5:CC5 is in asccending order and can be kept that way, try...

=IF(HLOOKUP(E3,$AV$5:$CC$7,1,1)=E3,HLOOKUP(E3,$AV$5:$CC$7,3,1),"Not Found")

Note. TRUE is also 1 in Excel.