Conditional Formatting and VLOOKUP Function

 Conditional Formatting and VLOOKUP Function

Conditional Formatting and VLOOKUP Function

Conditional formatting as the name suggests does formatting based on some conditions. The formatting is applied only if the specified conditions are met. Use the data given below to illustrate the concept.

If you wish to highlight details of Sales Representatives who has completed 2 years in the company, then select the cells to which you wish to apply formatting and go to HomeStylesConditional FormattingHighlight Cells RulesEqual To and type 2 in the first text box and choose the format you wish to apply from the dropdown. If you choose the condition as Light Red Fill with Dark Red Text and click OK, then your data would look as follows.

You could find many more useful condition options within Conditional Formatting. You can format certain number of items from top or bottom in a column, get values containing particular text, get dates matching to specified values and so on with conditional formatting. You have also the freedom to add new rules by going to HomeStylesConditional FormattingNew Rule. Thus, you are not restricted to limit your conditions with what Excel offers. Conditional formatting can make your Excel file more professional and clear.

VLOOKUP is a very useful function in Excel and it is used to look up values that are listed in column format in a table for a given value (say for key). HLOOKUP is a similar function that is used to look up values in rows. Suppose you run a computer sales company and you store the stock details of computers in an Excel file. Instead of saving the full name of the computer, you use a particular code for each computer and in another file you wish to retrieve the computer name for respective codes. This can be easily achieved by using a VLOOKUP function. Use the following data to illustrate the concept.

If you wish to get the details of computer with code LE, then you can achieve this simply with VLOOKUP function. How VLOOKUP works? When you provide a specific code, then Excel scans all codes in the column A to find the match and returns value from the same row. If you make the above details as a table, then you can use VLOOKUP function dynamically. So, select the data and save it as a table named Stock.

If you wish to get the details in another sheet, make the other sheet as follows.

Now, click the cell B2 and go to FormulasFunction LibraryLookup &ReferenceVLOOKUP. It will open a window as follows.

Lookup_value is the value that is to be looked up. In the example, lookup value is the code value added in second sheet, that is cell A2. Table_Array is the table where the value is to be searched. In this case, Table_array is Stock as that is the name given to the table. Col_index_num is the column of which value we wish to return. Here, we wish to return the description for corresponding code. In the example, Description is the second column. If you wish to return an error when you search a code that does not exist in the data table, then you need to set the value as FALSE. After completing the entries, your window would look as follows and then click OK.

Now, your result would be shown as

You can copy the formula in B2 to as many cells as you want. After copying the formula to B3, if you type GT in cell A3, then you will get the value as #N/A with value not available error. Here, though a very simple example is used to illustrate the concept, VLOOKP and HLOOKUP is used vastly to accomplish great things.

Read More about Excel here

Related post

Leave a Reply