Microsoft Excel waxaa laga yaabaa inay soo celiso qalad markaad gelisay qiime ama aad isku daydo inaad sameyso ficil ay ku guuldareysatay inay fahmaan. Waxa jira dhawr nooc oo khaladaad ah, khalad walbana waxa uu la xidhiidhaa noocyo gaar ah oo khaladaad ah oo aad samaysay.

Cilada #N/A waa qaladka caadiga ah ee Excel . Waxay soo baxdaa markaad si khaldan u tixraacday xogta Tusaale ahaan, xogta la tixraacay ee aan jirin ama ka jirta meel ka baxsan miiska raadinta, waxay samaysay khalad higaad ee qiimaha raadinta, ama ku daray jilaa dheeraad ah qiimaha raadinta (comma, apostrophe, ama xitaa dabeecad bannaan).

Maadaama uu khaladku dhaco marka aad si khaldan u tixraacdo qiimaha raadinta, inta badan waxay la xidhiidhaa hawlaha raadinta sida LOOKUP , VLOOKUP , HLOOKUP , iyo shaqada MATCH . Aynu eegno sababaha, tusaale, iyo hagaajinta qaar ee khaladka #N/A.

Sababaha #N/A Khaladka

Kuwa soo socda waa sababaha keeni kara khalad #N/A xaashida shaqada:

• Waxaad si khaldan u qortay qiimaha raadinta (ama gelisay jilaa boos oo dheeri ah)
• Waxaad si khaldan u higgaadisay qiimaha miiska raadinta (ama gelisay jilaa boos oo dheeri ah)
• Kala duwanaanshaha raadinta si khaldan ayaa loo geliyey qaacidada
• Waxaad u isticmaashay nooc xog ka duwan qiimaha raadinta ee ka jira miiska raadinta (ie, qoraalka la isticmaalay halkii lambarro^{(text instead of numbers)} )
• Qiimaha raadinta ee aad gelisay lagama helin miiska raadinta

Tusaalaha #N/A Khaladka

Aan u isticmaalno shaqada VLOOKUP^{(use the VLOOKUP function)} tusaale ahaan si aan u fahanno sida aad ugu dambayn karto qaladka #N/A ka dib markaad isticmaasho hawlaha Excel sida LOOKUP , HLOOKUP , ama MATCH maadaama ay wadaagaan qaab dhismeed isku mid ah.

Tusaale ahaan, waxaad sheegtaa inaad haysatid liis dheer oo shaqaale ah iyo gunnooyinkooda oo ku qoran buug- shaqeedka Excel^(Excel) . 

Waxaad isticmaashaa qaacidada VLOOKUP , geli mid khuseeya [lookup_value] kaas oo aad geliso tixraac unug (unug D4), qeex [miiska_array]^{([table_array] )} (A2:B7), oo qeex [col_index_num] (2). 

Doodda kama dambaysta ah ee loo yaqaan [range_lookup] , waa inaad isticmaashaa 1 (ama TRUE ) si aad u barato Excel^(Excel) si loo helo ciyaar sax ah. Dejinta 2 (ama BEEN^(FALSE) ) waxay tilmaami doontaa Excel inuu raadiyo kulan qiyaas ah, kaas oo ku siin kara wax soo saar khaldan.

Ka soo qaad^(Suppose) inaad diyaarisay qaacido lagu heli karo gunno shaqaale tiro yar, laakiin aad qalad u samaysay qiimaha raadinta. Waxaad ku dambayn doontaa cilad #N/A sababtoo ah Excel ma awoodi doono inuu helo wax u dhigma qiimaha miiska raadinta.

Haddaba, maxaad samaynaysaa si aad u saxdo khaladkan? 

Sida loo hagaajiyo #N/A Khaladka

Waxaa jira dhowr siyaabood oo lagu cilad bixiyo #N/A, laakiin hagaajinta ugu horrayn waxaa loo kala saari karaa laba hab:

1. Sixitaanka gelinta^{(Correcting the inputs)}
2. Dabinta qaladka^{(Trapping the error)}

Sixitaanka gelinta^{(Correcting the inputs)}

Fikrad ahaan, waa inaad aqoonsataa sababta qaladka adigoo isticmaalaya sababaha hore loogu taxay casharkan. Hagaajinta sababta waxay hubin doontaa inaadan kaliya ka takhalusin qaladka laakiin sidoo kale inaad hesho wax soo saar sax ah.

Waa inaad ku bilawdaa sababaha ku taxan hagahan liis hubin ahaan. Samaynta tan waxay kaa caawin doontaa inaad hesho gelinta khaldan ee aad u baahan tahay inaad hagaajiso si aad meesha uga saarto khaladka. Tusaale ahaan, waxay noqon kartaa qiime khaldan, xaraf boos oo dheeri ah, ama qiyam leh nooca xogta ee miiska raadinta.

Dabinta qaladka^{(Trapping the error)}

Haddii kale, haddii aad rabto inaad kaliya^{(just )} tirtirto khaladaadka xaashida shaqada adiga oo aan ku dhibin shakhsi ahaan u hubinta khaladaadka, waxaad isticmaali kartaa qaabab badan oo Excel ah. Qaar ka mid ah hawlaha ayaa si gaar ah loo abuuray si loo xakameeyo khaladaadka, halka kuwa kale ay kaa caawin karaan inaad dhisto syntax macquul ah iyadoo la adeegsanayo hawlo badan si loo tirtiro khaladaadka.

Waxaad dabin kartaa qaladka #N/A adoo isticmaalaya mid ka mid ah hawlaha soo socda:

• Shaqada IFERROR^{(IFERROR Function)}
• Shaqada IFNA^{(IFNA Function)}
• Isku darka Shaqada ISERROR iyo Haddi Shaqada^{(A Combination of ISERROR Function and IF Function)}
• Shaqada TRIM^{(TRIM Function)}

1. Shaqada IFERROR^{(1. IFERROR Function)}

Shaqada IFERROR waxaa la abuuray iyadoo ujeedadu tahay kaliya ee lagu beddelayo wax soo saarka unug soo celiya qalad.

Isticmaalka shaqada IFERROR waxay kuu ogolaaneysaa inaad geliso qiime gaar ah oo aad rabto in unuggu muujiyo halkii qalad. Tusaale ahaan, haddii aad ku leedahay cilad #N/A ah unugga E2 markaad isticmaalayso VLOOKUP , waxaad buuxin kartaa qaacidada oo dhan shaqada IFERROR , sida:

IFERROR (VLOOKUP(E4,B2:C7,2,1),"Shaqaale lama helin"^{(IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)}

Haddii shaqada VLOOKUP ay keento qalad, waxay si toos ah u soo bandhigi doontaa xarafka qoraalka " Shaqaalaha^(Employees) lama helin" halkii qaladka.

Waxa kale oo aad isticmaali kartaa xadhig madhan adiga oo si fudud gelinaya laba calaamadood ("") haddii aad rabto in aad muujiso unug maran marka qaacidadu soo noqoto khalad.

Ogsoonow in shaqada IFERROR ay u shaqeyso dhammaan khaladaadka. Marka, tusaale ahaan, haddii qaacidada aad buul ku dhexjirtay shaqada IFERROR ay soo celiso qaladka # DIV , IFERROR weli way dabinaysaa khaladka oo waxay soo celin doontaa qiimihii dooda u dambaysay.

2. Shaqada IFNA^{(IFNA Function)}

Shaqada IFNA waa nooc gaar ah oo ka mid ah shaqada IFERROR laakiin waxay u shaqeysaa si^(exactly) isku mid ah. Farqiga kaliya ee u dhexeeya labada hawlood ayaa ah in shaqada IFERROR ay dabinayso ^(IFERROR)dhammaan^(all) khaladaadka, halka shaqada IFNA ay kaliya dabinayso khaladaadka #N/A.

Tusaale ahaan, qaacidada soo socota ayaa shaqayn doonta haddii aad leedahay qaladka VLOOKUP^(VLOOKUP) #N/A, laakiin ma aha khalad # VALUE ah:

IFNA(VLOOKUP(E4,B2:C7,2,1),"Shaqaale lama helin"^{(IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)}

3. Isku darka Shaqada ISERROR iyo HADDII Hawsha^{(A Combination of the ISERROR Function and the IF Function)}

Siyaabo kale oo lagu dabin karo qaladka waa in la isticmaalo shaqada ISERROR oo ay la socoto shaqada IF. Waxay asal ahaan u shaqeysaa sida shaqada IFERROR , taas oo ku tiirsan shaqada ISERROR si loo ogaado qaladka, iyo shaqada IF si loo soo saaro wax soo saar ku saleysan imtixaan macquul ah.

Isku-darku wuxuu la shaqeeyaa dhammaan^(all) khaladaadka sida shaqada IFERROR , ma aha oo kaliya shaqada #N/A. Waa kuwan tusaale ahaan sida uu u ekaan doono syntax-ka marka la dabinayo Excel VLOOKUP^{(Excel VLOOKUP)} #N/A qaladka IF iyo ISERROR :

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)

4. Shaqada TRIM^{(TRIM Function)}

Waxaan horay uga wada hadalnay in xarfaha bannaan ee si aan ku talagal ahayn loo geliyo qiimeynta ay keeni karto qalad #N/A ah. Si kastaba ha ahaatee, haddii aad haysatid liis dheer oo qiimayaal raadin ah oo horeba loogu shubay xaashidaada shaqo, waxaad isticmaali kartaa shaqada TRIM halkii aad ka saari lahayd jilaha booska qiimihii raadin kasta si gaar ah.

Marka hore, samee tiir kale si aad u jar jarto booska hogaaminta iyo raadraaca magacyada adoo isticmaalaya shaqada TRIM :

Kadibna, u isticmaal safka cusub ee magacyada sida qiyamka raadinta ee shaqada VLOOKUP .

Hagaaji #N/A Khaladka Macros

Ma jiro qaacido gaar ah ama jid gaaban oo aad isticmaali karto si aad u hagaajiso khaladaadka #N/A ee makro. Maadaama aad u badan tahay inaad ku dartay dhowr hawlood gudaha makro-kaaga markii aad abuurtay^{(macro when you created it)} , waxaad u baahan doontaa inaad hubiso doodaha loo isticmaalo hawl kasta oo aad xaqiijiso haddii ay sax yihiin si loo hagaajiyo qaladka #N/A ee maco.

Khaladaadka N/A Waa La Hagaajiyay

Hagaajinta khaladaadka #N/A ma aha wax adag marka aad fahanto waxa sababa. Haddii aadan aad uga walaacsanayn wax-soo-saarka oo aadan rabin in qaacido ay keento khalad, waxaad isticmaali kartaa hawlaha sida IFERROR iyo IFNA si aad si sahal ah wax uga qabato khaladka #N/A.

How to Fix #N/A Errors in Excel Formulas like VLOOKUP

Microsoft Excel may return an error when you input a value or try to рerform an actiоn that it fails to understand. There are several types of errorѕ, and each error is associated with spеcific types of mistakes you might have made.

The #N/A error is a standard Excel error. It appears when you’ve referenced data incorrectly. For example, referenced data that doesn’t exist or exists outside of the lookup table, made a spelling error in the lookup value, or added an extra character in the lookup value (a comma, apostrophe, or even a space character).

Since the error occurs when you’ve incorrectly referenced a lookup value, it’s most commonly associated with lookup functions like LOOKUP, VLOOKUP, HLOOKUP, and the MATCH function. Let’s look at the reasons, an example, and some fixes for the #N/A error.

Reasons for #N/A Error

Following are the reasons that can cause a #N/A error on your worksheet:

• You’ve misspelled a lookup value (or inserted an extra space character)
• You’ve misspelled a value in the lookup table (or inserted an extra space character)
• The lookup range has been entered incorrectly into the formula
• You’ve used a different data type for the lookup value than the one that exists in the lookup table (i.e., used text instead of numbers)
• The lookup value you entered was not found in the lookup table

Example of #N/A Error

Let’s use the VLOOKUP function as an example to understand how you might end up with a #N/A error after using Excel functions like LOOKUP, HLOOKUP, or MATCH since they share a similar syntax structure.

For instance, say you have a long list of employees and their bonuses listed in an Excel workbook. 

You use the VLOOKUP formula, enter a relevant [lookup_value] for which you insert a cell reference (cell D4), define the [table_array] (A2:B7), and define [col_index_num] (2). 

For the final argument called the [range_lookup], you should use 1 (or TRUE) to instruct Excel to obtain an exact match. Setting it to 2 (or FALSE) will instruct Excel to look for an approximate match, which can give you an incorrect output.

Suppose you’ve set up a formula for obtaining bonuses for a select few employees, but you misspell the lookup value. You’ll end up with a #N/A error because Excel won’t be able to find an exact match for the value in the lookup table.

So, what must you do to fix this error? 

How to Fix #N/A Error

There are several ways for troubleshooting the #N/A error, but the fixes can primarily be categorized into two approaches:

1. Correcting the inputs
2. Trapping the error

Correcting the inputs

Ideally, you should identify the cause of the error using the reasons listed previously in this tutorial. Fixing the cause will ensure that you’re not just getting rid of the error but also getting the correct output.

You should start by using the reasons listed in this guide as a checklist. Doing this will help you find the incorrect input that you need to fix to eliminate the error. For example, it could be a misspelled value, an extra space character, or values with an incorrect data type in the lookup table.

Trapping the error

Alternatively, if you want to just eliminate errors from your worksheet without bothering with individually checking for mistakes, you can use several Excel formulas. Some functions have been created specifically to trap errors, while others can help you construct a logical syntax using multiple functions to eliminate errors.

You can trap the #N/A error using one of the following functions:

• IFERROR Function
• IFNA Function
• A Combination of ISERROR Function and IF Function
• TRIM Function

1. IFERROR Function

The IFERROR function was created with the sole purpose of changing the output for a cell that returns an error.

Using the IFERROR function allows you to enter a specific value that you want a cell to show instead of an error. For instance, if you have a #N/A error in cell E2 when using VLOOKUP, you can nest the entire formula into an IFERROR function, like so:

IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

If the VLOOKUP function results in an error, it will automatically display the text string “Employees not found” instead of the error.

You can also use an empty string by simply inserting two quotation marks (“”) if you want to display a blank cell when the formula returns an error.

Note that the IFERROR function works for all errors. So, for instance, if the formula you’ve nested inside the IFERROR function returns a #DIV error, IFERROR will still trap the error and return the value in the last argument.

2. IFNA Function

The IFNA function is a more specific version of the IFERROR function but works exactly the same way. The only difference between the two functions is that the IFERROR function traps all errors, while the IFNA function only traps #N/A errors.

For instance, the following formula will work if you have a VLOOKUP #N/A error, but not for a #VALUE error:

IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”

3. A Combination of the ISERROR Function and the IF Function

Another way to trap an error is to use the ISERROR function along with the IF function. It essentially works like the IFERROR function, in that it relies on the ISERROR function to detect an error, and the IF function to render output based on a logical test.

The combination works with all errors like the IFERROR function, not just the #N/A function. Here’s an example of what the syntax will look like when trapping an Excel VLOOKUP #N/A error with the IF and ISERROR functions:

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)

4. TRIM Function

We discussed earlier that a space character inserted inadvertently in the lookup value can result in a #N/A error. However, if you have a long list of lookup values already populated into your worksheet, you can use the TRIM function instead of removing the space character from each lookup value individually.

First, create another column to trim leading and trailing spaces in the names using the TRIM function:

Then, use the new column of names as the lookup values in the VLOOKUP function.

Fix #N/A Error in Macros

There’s no specific formula or shortcut you can use to fix #N/A errors in a macro. Since you likely added several functions into your macro when you created it, you’ll need to check the arguments used for each function and verify if they’re correct to fix an #N/A error in a maco.

#N/A Errors Fixed

Fixing #N/A errors isn’t that difficult once you understand what causes them. If you’re not too concerned about the output and just don’t want a formula to result in an error, you can use functions like IFERROR and IFNA to easily tackle the #N/A error.

Related posts

• Goorta la Isticmaalayo Tusaha-Match Bedelkii VLOOKUP ee Excel

• Sida loo hagaajiyo Bookmark aan lagu qeexin qalad gudaha Word

• Sida loo tirtiro khadadka banaan ee Excel

• Sida Loo Hagaajiyo Muuqaalka Ku dheggan Profile-ka Loading

• Hagaaji MS Office "Ma Xaqiijin Karo Shatiga" Fariinta Khaladka

• Sida Loo Hagaajiyo Raadinta Muuqaalka Aan Shaqeynin

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• Hagaha VBA Sare ee MS Excel

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Sida Looga Saaro Safka Kooban Ee Excel

• Waa maxay VBA Array ee Excel iyo Sida loo Barnaamijyo Mid

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida Loo Raadiyo Excel

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis