Lookup Basic"s

I though i should have a play with some basic lookup functions.

And i now understand the basic.
=LOOKUP($C1,$A1:$A1,$B1:$B1)

Though i wanted to rid the Na error from it when C is blank

I seen a few ways, And wanted to try the method of using Countif becouse it can be used withen the same cell without using extra cells like another method i seen "=IF(ISNA(A1),0,A1)"

what i know of countif
This function counts the number of items which match criteria set by the user.
=IF(COUNTIF($A1,"")),LOOKUP($C1,$A1:$A1,$B1:$B1),"")
so how the heck can the countif function be used to rid the NA of a lookup. i just cant see a relationship between counting a lookup.

Is there a better option that can be used withen the same cell formular.

Thankyou.

Without getting too deep into the actual formula you're using I would make the following points:

1 - an IF works like:

Please Login or Register  to view this content.

2 - In XL only 0 = FALSE all other numeric values are TRUE

so if the test results in a number you can use that number as the "boolean" test - ie is it true/false... it will be TRUE only if not zero

Please Login or Register  to view this content.

so the above says if there are blanks in the COUNTIF range then return a Null rather than conduct the LOOKUP.

Originally Posted by D_Rennie

Is there a better option that can be used withen the same cell formular.

There are simpler ways of testing based on contents of A1, eg COUNT / COUNTA depending on data type.

Hi D_Rennie,

More on what DonkeyOte said:

- - -

When COUNTIF checks for a value in a list, and it finds no entries of that value, the count result is zero. This is a valid check result.

When LOOKUP checks for a value in a list, and it finds no entries of that value, it must return an indication to say the value is not anywhere in the list, or "#NA".

In an Excel IF statement, zero is also considered to equal "FALSE". If your IF statement truth test (the COUNTIF function) returns a zero, then the IF statement considers the test to be false and gives you the appropriate answer as "".

If you're using Excel 2007, then you should be able to use the IFERROR function. From your example:

Please Login or Register  to view this content.

Hope that helps.

Cheers,

The only real downside to an IFERROR based approach is that you're always conducting the formula, in this instance this may not be a big deal but if your formula becomes such that is "expensive" performance wise then in reality it is in your interests to avoid calculating unnecessarily in the first instance - ie if the determining factor is a quick efficient check elsewhere (IF(COUNT(A1),...)) then you should do that check initially via IF so as to avoid conducting the main "expensive" calculation which you know will only return an error.

(IFERROR is also a "catch-all" handler but that can be saved for another day...)