.jpg)
%20Abstract%20Background%20112024%20SOURCE%20Amazon.jpg)
![[The AI Show Episode 142]: ChatGPT’s New Image Generator, Studio Ghibli Craze and Backlash, Gemini 2.5, OpenAI Academy, 4o Updates, Vibe Marketing & xAI Acquires X](https://www.marketingaiinstitute.com/hubfs/ep%20142%20cover.png)
![From drop-out to software architect with Jason Lengstorf [Podcast #167]](https://cdn.hashnode.com/res/hashnode/image/upload/v1743796461357/f3d19cd7-e6f5-4d7c-8bfc-eb974bc8da68.png?#)
.png?#)
.jpg?#)
_Christophe_Coat_Alamy.jpg?#)
![Rapidus in Talks With Apple as It Accelerates Toward 2nm Chip Production [Report]](https://www.iclarified.com/images/news/96937/96937/96937-640.jpg)
Mastering Excel's IFNA: Efficient Value Retrieval and VLOOKUP Integration
In this article, master Excel's IFNA function to efficiently retrieve the correct value when faced with two options. Also, explore using VLOOKUP with IFNA for enhanced data retrieval and management! IFNA function Microsoft explains that the IFNA function returns the value you specify if a formula returns the #N/A error value; otherwise, it returns the result of the formula. The IFNA function comes in handy when you need to return one out of two values. An easy illustration would be a retail price and sale price for a product: If the product is on sale, return the sale price; otherwise, return the retail price. It’s important to note that IFNA function is not checking for greater or less than values; its role is to handle errors by providing an alternative value when a formula results in an #N/A error. Let’s take a look at the syntax of the formula: =IFNA(value, value_if_na) value: This is the argument or formula you want to check for the #N/A error. value_if_na: This is the value you want to return if the formula results in an #N/A error. In this Star Wars-themed example, we explore a selection of iconic weapons, each with its own unique pricing structure. Item Code (Column A): Quick reference for each weapon. Item Description (Column B): Brief overview of the weapon. Retail Price (Column C): Standard cost of each item. Adjusted Price (Column D): Discounts or special pricing; "N/A" if not applicable. Final Price (Column E): In row 2, the formula in the Final Price column uses =IFNA(D2, C2) to default to the "Retail Price" if the "Adjusted Price" is unavailable. Item Code Item Description Retail Price Adjusted Price Final Price 001 Lightsaber 250 Credits 200 Credits 200 Credits 002 Blaster Rifle 150 Credits N/A 150 Credits 003 Thermal Detonator 500 Credits 450 Credits 450 Credits 004 Vibroblade 300 Credits N/A 300 Credits 005 Bowcaster 400 Credits 450 Credits 450 Credits In this example, the "Final Price" for the Bowcaster reflects the "Adjusted Price" of 450 Credits, demonstrating that the IFNA function can return the adjusted price regardless of whether it is higher or lower. What If Both Retail and Adjusted Prices Are Available? In the Star Wars-themed example, the IFNA function first checks the "Adjusted Price" to see if it is available and does not result in an #N/A error. If the "Adjusted Price" is valid and present, the function will return this value. This means that the "Adjusted Price" is prioritized because it might reflect a discount or special offer that you want to apply. For instance, consider the pricing for a Lightsaber: Item Code Item Description Retail Price Adjusted Price Final Price 001 Lightsaber 250 Credits 200 Credits 200 Credits In this example, the "Final Price" for the Lightsaber reflects the "Adjusted Price" of 200 Credits, demonstrating how the IFNA function prioritizes the "Adjusted Price" when it is available. However, if the "Adjusted Price" is missing or results in an #N/A error, the IFNA function will then return the "Retail Price" instead. The "Retail Price" acts as a fallback option, ensuring that you always have a price to display, even if the "Adjusted Price" is unavailable. This approach allows you to seamlessly handle pricing scenarios where you want to use a special price when available, but default to the standard price if not. Real-World Use Case At work, I needed to automate pricing bid sheets sent out to customers weekly. I accomplished this by creating Excel templates for each customer that take exported system information and match the current prices to the items via a VLOOKUP formula; I wrote a detailed article on using VLOOKUP. However, one bid sheet worked off of two price lists; price list one had the default price while price list two had the priotized price. This means that if the identical item has different prices on both price lists, the price on price list two is correct (regardless of whether the price is higher or lower). So, I was able to retrieve the correct price by using the IFNA function. I set up the formula to first check the price from list two (the prioritized price). If this price was available and not an #N/A error, it would be used. If it was unavailable, the formula would default to the price from list one (the default price). This simple yet effective approach ensures the correct price is displayed in the customer’s bid sheet. In this table, the "Final Price" reflects the "Prioritized Price" from Price List Two if available. Otherwise, it defaults to the "Default Price" from Price List One. Item Code Item Description Price List One (Default Price) Price List Two (Prioritized Price) Final Price 001 Case of Apples N/A $35.00 $35.00 002 Case of Bananas $25.00 N/A $25.00 003 Case of Carrots $30.00 $28.00 $28.00 004 Case of Lettuce N/A $38.00 $38.00 005 Case of Tomatoes $45.00 $47.00 $47.00
In this article, master Excel's IFNA function to efficiently retrieve the correct value when faced with two options. Also, explore using VLOOKUP with IFNA for enhanced data retrieval and management!
IFNA function
Microsoft explains that the IFNA function returns the value you specify if a formula returns the #N/A error value; otherwise, it returns the result of the formula.
The IFNA function comes in handy when you need to return one out of two values. An easy illustration would be a retail price and sale price for a product: If the product is on sale, return the sale price; otherwise, return the retail price.
It’s important to note that IFNA function is not checking for greater or less than values; its role is to handle errors by providing an alternative value when a formula results in an #N/A error.
Let’s take a look at the syntax of the formula:
=IFNA(value, value_if_na)
value: This is the argument or formula you want to check for the #N/A error.
value_if_na: This is the value you want to return if the formula results in an #N/A error.
In this Star Wars-themed example, we explore a selection of iconic weapons, each with its own unique pricing structure.
Item Code (Column A): Quick reference for each weapon.
Item Description (Column B): Brief overview of the weapon.
Retail Price (Column C): Standard cost of each item.
Adjusted Price (Column D): Discounts or special pricing; "N/A" if not applicable.
Final Price (Column E): In row 2, the formula in the Final Price column uses
=IFNA(D2, C2)to default to the "Retail Price" if the "Adjusted Price" is unavailable.
| Item Code | Item Description | Retail Price | Adjusted Price | Final Price |
|---|---|---|---|---|
| 001 | Lightsaber | 250 Credits | 200 Credits | 200 Credits |
| 002 | Blaster Rifle | 150 Credits | N/A | 150 Credits |
| 003 | Thermal Detonator | 500 Credits | 450 Credits | 450 Credits |
| 004 | Vibroblade | 300 Credits | N/A | 300 Credits |
| 005 | Bowcaster | 400 Credits | 450 Credits | 450 Credits |
In this example, the "Final Price" for the Bowcaster reflects the "Adjusted Price" of 450 Credits, demonstrating that the IFNA function can return the adjusted price regardless of whether it is higher or lower.
What If Both Retail and Adjusted Prices Are Available?
In the Star Wars-themed example, the IFNA function first checks the "Adjusted Price" to see if it is available and does not result in an #N/A error. If the "Adjusted Price" is valid and present, the function will return this value. This means that the "Adjusted Price" is prioritized because it might reflect a discount or special offer that you want to apply.
For instance, consider the pricing for a Lightsaber:
| Item Code | Item Description | Retail Price | Adjusted Price | Final Price |
|---|---|---|---|---|
| 001 | Lightsaber | 250 Credits | 200 Credits | 200 Credits |
In this example, the "Final Price" for the Lightsaber reflects the "Adjusted Price" of 200 Credits, demonstrating how the IFNA function prioritizes the "Adjusted Price" when it is available.
However, if the "Adjusted Price" is missing or results in an #N/A error, the IFNA function will then return the "Retail Price" instead. The "Retail Price" acts as a fallback option, ensuring that you always have a price to display, even if the "Adjusted Price" is unavailable.
This approach allows you to seamlessly handle pricing scenarios where you want to use a special price when available, but default to the standard price if not.
Real-World Use Case
At work, I needed to automate pricing bid sheets sent out to customers weekly. I accomplished this by creating Excel templates for each customer that take exported system information and match the current prices to the items via a VLOOKUP formula; I wrote a detailed article on using VLOOKUP.
However, one bid sheet worked off of two price lists; price list one had the default price while price list two had the priotized price. This means that if the identical item has different prices on both price lists, the price on price list two is correct (regardless of whether the price is higher or lower).
So, I was able to retrieve the correct price by using the IFNA function. I set up the formula to first check the price from list two (the prioritized price). If this price was available and not an #N/A error, it would be used. If it was unavailable, the formula would default to the price from list one (the default price). This simple yet effective approach ensures the correct price is displayed in the customer’s bid sheet.
In this table, the "Final Price" reflects the "Prioritized Price" from Price List Two if available. Otherwise, it defaults to the "Default Price" from Price List One.
| Item Code | Item Description | Price List One (Default Price) | Price List Two (Prioritized Price) | Final Price |
|---|---|---|---|---|
| 001 | Case of Apples | N/A | $35.00 | $35.00 |
| 002 | Case of Bananas | $25.00 | N/A | $25.00 |
| 003 | Case of Carrots | $30.00 | $28.00 | $28.00 |
| 004 | Case of Lettuce | N/A | $38.00 | $38.00 |
| 005 | Case of Tomatoes | $45.00 | $47.00 | $47.00 |
