Excel Error 3- #N/A

Excel Error #N/A

Not applicable error shows #n/a, indicating that a formula can't find what it's been asked to look for.

The key point here is to look for the most common cause of the non-preconchemical error is with xlookup, vlookup, hlookup, lookup, or match functions. If a formula can't find a reference value.

Let's take a look at some examples. In this range A2 to B7, I have headers fruit and price. Let's say if I want to look up Banana with vlookup function.

So, for vlookup functions is the syntax vlookup followed by lookup value which is D2 and table array is A3 to B7. And column is number 2 because I want to look up for the price. And I want exact match so I put False. So, for the result is 1.2, it matches the range which is the price of banana. And I will make the formula text here for reference.

If I want to use the same formula for pairs, I start typing the vlookup functions. And you can see non-applicable error shows. This is because vlookup function can't find Pear in this range.

Solution 1: Make sure the value exists

Now we know what causes non-applicable error then how we can correct that?

First, make sure the lookup value exists in the source data. Let's say we add Pear information in the range. So, Pear and we make it 1.32. And you can see the result shows.

Solution 2: Make sure there is no space

Second, make sure there is no space. In the example, I already have pear in my range but my lookup function is not applicable. For this issue, make sure the lookup value has no space. Let's say if I double click the lookup value here, I can find there is a space after the pear. That will make the Vlookup Function treat its difference value.

So two solutions. One, just simply remove the space and you can see the result shows. And the second, we can use Trim Function which is TRIM, Trim functions. Trim Function will remove the space of the text. You can see if I use Trim functions, I can get the pear without a space. So I can simply use Vlookup again and lookup value is Trim functions and D26. Then use the same range, number 2 and False. Then you can find the information of pear.

Solution 3: Incorrect Value Type

Sometimes not applicable error is due to incorrect value type. In this example, the price is my lookup value and I want to know what's the fruits of the price. Let's say I want to look up the Price 1.2 Use the Vlookup Function And we can get the price 1.2 is banana.

Let's say if I want to look up for price 3 And you also use the same Vlookup Function But not-applicable error happens. You can see the corner of this cell there is an error remind. Say this cell is a text instead of value. In this case, you can choose this convert to numbers to make the text convert to numbers. And when you did that, you can see the result comes correctly and non-applicable functions disappear.