Look up problem

  • Thread starter Thread starter Beergut
  • Start date Start date
B

Beergut

Part 1
I have this look up in cell H18.
=LOOKUP(RIGHT(I18,2),$G$68:$H$75)
Cell I18 is formated as text but contains numbers like 123405. The
RIGHT(I18,2) takes the last 2 digits (the 05) and does the lookup.

This works except when a number that is not in the lookup range is
entered in I18. For example, if 95 is entered the lookup returns the
lookup for 05.

My first question is how can I return only valid input in the LU
range.

Part2
If nothing is in Cell I18 then #N/A appears in H18. The #N/A looks
awful.

The second question is how can I show nothing in H18 when I18 is
blank.

I have Excel 97 and I'll never be a programmer.
 
Hi Beergut

don't need to be a programmer ... :)

1. i would use a combination of INDEX & MATCH instead of LOOKUP to get it
"exact"
=INDEX($G$68:$H$75,MATCH(VALUE(RIGHT(I18,2)),$G$68:$G$75,0),2)

if it's not found it will return a #NA error - (which is then the same
question as your #2)

use an IF formula to deal with the #NA error, -
IF(ISNA(formula),"",formula)
e.g.
=IF(ISNA(MATCH(VALUE(RIGHT(I18,2)),$G$68:$G$75,0)),"",INDEX($G$68:$H$75,MATCH(VALUE(RIGHT(I18,2)),$G$68:$G$75,0),2))
(instead of the formula under 1 above, here i'm only testing for the ISNA on
the match part because this is where the "error" will occur) - this will
return nothing if the value is not found ... or you can replace the ,"", in
the formula above with ,"value not found", if you want these words to
appear.

for the other situation use
=IF(I18="","",your formula)
or
=IF(ISBLANK(I18),"",your formula)
or
=IF(ISNA(your formula),"",your formula)

Hope this helps
Cheers
JulieD
 
Hello JulieD.

The formulas return #NALUE! if I18 is blank or has a letter and #NA
for any number put in cell I18.

I hate computers.

BG
 
Hi Beergut

could you confirm that the following formula is the one returning errors
=IF(ISNA(MATCH(VALUE(RIGHT(I18,2)),$G$68:$G$75,0)),"",INDEX($G$68:$H$75,MATCH(VALUE(RIGHT(I18,2)),$G$68:$G$75,0),2))

maybe cut & paste your exact formula in the reply so i can have a look at
it.

Cheers
JulieD
 
Back
Top