lookup function

[Scottie]

Many THX for the previous help...
If the function doesn't find the lookup value in the
lookup array then it enters the previous lookup array
entry. I want it to enter an "n/a" or "zero". Plus
according to help the array needs to be in alpha and
numeric order or it may not give correct value. How can I
get around this or is there a better function?

 

[Alan]

Use the fourth argument, instead of
=VLOOKUP(A1,F1:J20,2)
which will find the nearest match, use
=VLOOKUP(A1,F1:J20,2,FALSE)
which returns an exact match or #N/A, and the data doesn't need to be sorted
=IF(ISNA(VLOOKUP(A1,F1:J20,2,FALSE)),"Your error
message",VLOOKUP(A1,F1:J20,2,FALSE))
will give anything you want to display instead of #N/A