How to use IF and LOOKUP function in Excel

How to Use Conditional Formatting Using the LOOKUP and IF Function In Excel

If you are working on a large spreadsheet it is difficult to make sense of all the data manually.

 You can use the IF and LOOKUP function in Excel to make sense of the data for you.

Open Microsoft Excel on your Computer.

 

     1. Click On :New" at the leftside 

     2. And click on the "Create" button at the liftside

Enter information in the cells what to format? For example, you may have a list of 26 students and their exam grades.

 

You can enter all the corresponding letter grades manually. 

For example 

• 89 percent means    A
• 79 percent means    B
• 69 Percent means    C
• Less then 49 Percent   F

Or you can use the "IF" or "LOOKUP" function in Excel and have the grades entered automatically as you type the percentage.

Click on the cell where you would like to place Formula  "IF and LOOKUP" Function. 

Type:

=IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>49,"D","F")))) 

Notice that the letter in the roster will change live as you enter the grades.

 Hit Enter To see the Result.

Look for the small box with a plus(+) sign that pops up near the bottom of those cells. Click the arrow and choose "Fill Formatting Only." This will copy the type of formula you have just used, but move the values to the correct vertical row.

This IF function states that if B2 is > than 89 is true than assign a letter "A" grade. In the second example below, we will use the LOOKUP function. Type in =LOOKUP(B4,{40,45,60,65,80,85,90,95},{"D-","D","C-","C","B-","B","A-","A"})in B4 that corresponds to Dave. 

This will tell Excel to look-up the data in cell B4. This equation programs 40 percent to return a "D" grade and less then 40 return "F" Grade. Do the same for B5 and B6 or you can pull down on the fill handle so that the equation applies to all the students.