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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

**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

Please Login or Register  to view this content.

**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.