How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will get to know about various ways to create a formula in Excel that will place the word Yes easily. Sometimes we want a result as Yes depending on the criteria met or not. So, let’s start our main article to know more about the details.

Download Workbook


7 Ways to Create a Formula in Excel That Will Place the Word Yes

Here, we have the following dataset containing the sales records of a company, and using this dataset we will try to create some formulas to place the word Yes.

create a formula in Excel that will place the word yes

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using IF Function to Create a Formula to Place the Word Yes

Here, we will use the IF function to get Yes in the Formula column when the sales values are greater than 3000 for the products.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell D4

=IF(C4>3000,"YES","")

Here, C4 is the Sales value, C4>3000 is the logical condition and when the condition will be TRUE it will return YES otherwise a Blank.

IF Function

➤ Press ENTER and drag down the Fill Handle tool.

IF Function

As a result, we are getting YES for the sales values greater than $3,000.00.

create a formula in Excel that will place the word yes

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)


Method-2: Using Nested IF Functions to Create a Formula in Excel That Will Place the Word Yes

In this section, we are going to use Nested IF functions or one IF function within another one to get Yes for fulfilling multiple conditions like for the Region as North and for Sales values greater than 3000.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell E4

=IF(C4="North",IF(D4>3000,"YES",""),"")

Here, C4 is the Region, D4 is the Sales value, C4=”North ” is the first logical condition and when the condition will be TRUE it will go to another IF function, and D4>3000 is the second logical condition for this function. For not meeting the conditions we will get a Blank.

Nested IF Functions

➤ Press ENTER and drag down the Fill Handle tool.

Nested IF Functions

Afterward, you will get YES for the products which are in the North region and have a Sales value greater than $3,000.00.

Nested IF Functions


Method-3: Using IF, AND Functions to Create a Formula in Excel to Place the Word Yes

For having Yes when the multiple conditions like the Region as North and Sales value greater than 3000 will be met, we are using the AND function and the IF function here.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell E4

=IF(AND(C4="North",D4>3000),"Yes","")

Here, C4 is the Region, D4 is the Sales value, C4=”North ” and D4>3000 are the logical conditions, and AND will return TRUE for meeting both conditions otherwise FALSE. For TRUE we will get Yes and for FALSE we will get a Blank.

using AND, IF function

➤ Press ENTER and drag down the Fill Handle tool.

using AND, IF function

Finally, you will get Yes for the products which are in the North region and have a Sales value greater than $3,000.00.

create a formula in Excel that will place the word yes


Method-4: IF with OR Function to Create a Formula in Excel That Will Place the Word Yes

For meeting any one of the criteria between the Region as North and the Sales value greater than 3000, we will be using the OR function.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell E4

=IF(OR(C4="North",D4>3000),"Yes","")

Here, C4 is the Region, D4 is the Sales value, C4=”North ” and D4>3000 are the logical conditions, and OR will return TRUE for meeting any one of these conditions otherwise FALSE. For TRUE we will get Yes and for FALSE we will get a Blank.

using OR, IF function

➤ Press ENTER and drag down the Fill Handle tool.

using OR, IF function

Consequently, you will get Yes for the products when any one of the conditions is met here.

using OR, IF function


Method-5: Considering Dates to Create a Formula in Excel That Will Place the Word Yes

To have Yes when delivery dates are matched with today’s date (3/7/2022 as m/d/yyyy format) we will use the TODAY function with the IF function.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell D4

=IF(C4=TODAY(),"YES","")

Here, C4 is the Delivery Date, C4=TODAY() is the logical condition, and when the condition will be TRUE it will return YES otherwise a Blank.

checking dates

➤ Press ENTER and drag down the Fill Handle tool.

checking dates

In this way, we will get YES for the delivery dates which are equal to today’s date.

checking dates

Read More: How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)


Method-6: Combine Several Excel Functions to Create a Formula to Place the Word Yes

Here, we have a list of three products and we will check if the products of this list are available in the dataset, for their availability we will get Yes in the Available or Not column by using the VLOOKUP function, ISNA function, IF function.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell C13

=IF(ISNA(VLOOKUP(B13,$B$3:$D$10,3,FALSE)),"","YES")

Here, B13 is the lookup value, $B$3:$D$10 is the lookup range, 3 is the serial number of columns in the range from which we will extract the value for our corresponding lookup value and FALSE is for an exact match.

  • VLOOKUP(B13,$B$3:$D$10,3, FALSE) → returns the corresponding sales values for the products if the value is in the list and otherwise #N/A
    Output → 2260
  • ISNA(VLOOKUP(B13,$B$3:$D$10,3,FALSE)) becomes
    ISNA(2260) → returns TRUE for #N/A error and FALSE for other values.
    Output → FALSE
  • IF(ISNA(VLOOKUP(B13,$B$3:$D$10,3,FALSE)),””,”YES”) becomes
    IF(FALSE,””,”YES”) → returns YES for FALSE otherwise a Blank for TRUE.
    Output → YES

checking a list with VLOOKUP Function

➤ Press ENTER and drag down the Fill Handle tool.

checking a list with VLOOKUP Function

Finally, we will get YES for the available products on the list.

create a formula in Excel that will place the word yes


Method-7: Using IF, INDEX, MATCH, ISNA to Create a Formula in Excel That Will Place the Word Yes

In this section, we will check the available products in the list and get Yes for the available products by using the INDEX function, MATCH function, ISNA function, IF function.

create a formula in Excel that will place the word yes

Steps:
➤ Type the following formula in cell C13

=IF(ISNA(INDEX($D$4:$D$10,MATCH(B13,$B$4:$B$10,0))),"","YES")

Here, B13 is the lookup value, $B$4:$B$10 is the lookup array and 0 is for an exact match.

  • MATCH(B13,$B$4:$B$10,0) → returns the row or column index number of the lookup value in the data range.
    Output → 3
  • INDEX($D$4:$D$10, MATCH(B13,$B$4:$B$10,0)) → returns the information we actually want by using the return range $D$4:$D$10 and if the value is not present in the list then it will return #N/A
    Output → 2260
  • ISNA(INDEX($D$4:$D$10,MATCH(B13,$B$4:$B$10,0))) becomes
    ISNA(2260) → returns TRUE for #N/A error and FALSE for other values.
    Output → FALSE
  • IF(ISNA(INDEX($D$4:$D$10,MATCH(B13,$B$4:$B$10,0))),””,”YES”) becomes
    IF(FALSE,””,”YES”) → returns YES for FALSE otherwise a Blank for TRUE.
    Output → YES

checking a list with INDEX MATCH Function

➤ Press ENTER and drag down the Fill Handle tool.

checking a list with INDEX MATCH Function

Eventually, you will get YES for the available products on the list.

checking a list with INDEX MATCH Function

Read More: How to Create a Formula to Calculate Percentage in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to create a formula in Excel that will place the word Yes easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo