# HLOOKUP easy, but wrong if no exact match?

#### boznian

##### Board Regular
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
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
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
"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!

##### MrExcel MVP
"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...

Note. TRUE is also 1 in Excel.

Replies
4
Views
474
Replies
6
Views
232
Replies
21
Views
942
Replies
7
Views
813
Replies
1
Views
377

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.

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.

### Which adblocker are you using?

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

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