HLOOKUP easy, but wrong if no exact match?

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Jul 28, 2010
Messages
348
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

Board Regular
Joined
Mar 25, 2003
Messages
167
"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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
"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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,732
Messages
5,833,377
Members
430,206
Latest member
Sami Gaid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top