lookup function

**cassiopea** · 02-17-2011, 05:33 AM

Hi!

I am using LOOKUP Function.
When this function can't find the data, it returns NA(). Now, I want that it returns empty cell (i.e. "") if it can't find the data. I tried with this:

=IF(LOOKUP(O3;$J$2:$J$14;$C$2:$C$14)=NA();"";LOOKUP(O3;$J$2:$J$14;$C$2:$C$14)/1000)

but it still doesn't return EmptyCell but again #NA().

Is there another way to solve this problem?

Thanks in advance

**davesexcel** · 02-17-2011, 05:40 AM

Checkout,
=if(iserror()
Check out this explaination
http://spreadsheetpage.com/index.php...s_in_formulas/

**Colin Legg** · 02-17-2011, 05:45 AM

It's probably returning an NA() error because the value in O3 is less than the value in J2 (assume J2:J10 is sorted). If that's right then:

=IF(O3<J2,"",LOOKUP(O3,$J$2:$J$14,$C$2:$C$14)/1000)

**cassiopea** · 02-18-2011, 04:56 AM

Daves solution works just fine, but another problem appeared.

If a lookup_vector contains an error (NA()), then Lookup function returns an error too (#NAME) although there is a match value in lookup vector. I tried to name (define) lookup vector, but it still returns the error. Is there a solution?

**DonkeyOte** · 02-18-2011, 05:09 AM

Underlying errors in the lookup_vector will not cause the LOOKUP to fail per se - this feature is in fact one if it's main advantages

Underlying errors in the result_vector, however, would generate the same error value should the error be the appropriate result for the LOOKUP (based on lookup_vector match)

In your formula non numerics in the result_vector would generate #VALUE! errors given the explicit coercion taking place thereafter.

Perhaps you could post a sample which illustrates the problem "in action"

**cassiopea** · 02-18-2011, 06:57 AM

ok, now I have errors in result_ vector and LOOKUP returns an error although there is no error in lookup_vector nor in the match in the resulz_vector.

I attached an example.

**Colin Legg** · 02-18-2011, 07:04 AM

The issue is that the formulae in column D mean that the dates are not sorted. LOOKUP() relies on this.

If you change the formula in D4 to this and fill down you should be OK

=IF(A4>0,A4,"")

**DonkeyOte** · 02-18-2011, 07:06 AM

In addition to Colin's points above... if you want to perform an exact match on the date consider using a VLOOKUP with range_lookup of FALSE else use SUMIF.