Using the IF and VLOOKUP Functions
The IF function is used to create a formula that returns different results under different conditions. You use the VLOOKUP function to create a formula that picks a return value from a range of values depending on an input value. E.g. three salespeople did not meet the quota of $60,000, and therefore they should not receive a commission. However, the current formula in the Comm column calculates a commission for each salesperson. The syntax for the IF function, and the practical application example. You use the IF function to perform one calculation if a certain condition is true, and a different calculation if that condition is false.
IF(logical_test, value_if_true, value_if_false)
Logical_test is any value or expression resulting in TRUE or FALSE.
Value_if_true is the value (or formula resulting in a value) that will be returned if logical_test is TRUE.
Value_if _false is the value (or formula resulting in a value) that will be returned if logical_test is FALSE. For example, given the following function: IF(A4>5, A4/2,0)
If A4 =8, then logical_test (A4>5) is TRUE, and the function returns value_if_true (A4/2).
If A4 = 2, then logical_test (A4>5) is FALSE, and the function returns value_if_false (0).
The range F6:F10 has been cleared. E.g. to build a new formula that awards commissions only to those salespeople who meet the quota. Click on the Paste Function button. In the Function Category list, select Logical. In the Function Name list, select IF. Click on OK.
Now completing the three sections of the IF function dialog box. In the Logical_test box, type E6>=Quota in the value_if_true box, type E6*Comm_Rate in the Value_if_false box, type 0 (this formula will test whether the sales total value is greater than or equal to the quota value. If the test is true, a commission value will be calculated. If the test is false, the commission value will be assigned a zero value). Click on Ok.
A function’s arguments can be constant values or formulas. When a function’s argument is another function, it’s called a nested function. Nested IF functions have their place, but they also have their shortcomings. Rather than nest IF functions, you can use the VLOOKUP function. The VLOOKUP function finds and returns a value located in a certain row and column of a lookup table, as long as the table contains an orderly arrangement of information.
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup_value is the value that will be looked for in the first column the lookup table. It can be a value, reference, or text string.
Table_array is a reference to (or name of a reference to) the lookup table.
Vol_index_num is the column number in the lookup table from which value should be returned.
Range_lookup is an optional argument that specifies whether you require an exact match or an approximate match.
E.g. select the Bonus column contents. Click on the Paste Function button. In the Function Category list, select Lookup & Reference. Click on the vertical scroll bar below the scroll button. In the Function Name list, select VLOOKUP. (The structure and explanation of the VLOOKUP function will display at the bottom of the paste Function dialog box). Click on Ok. Then providing settings for the first three sections. In the Lookup_value box, type the cell ---- In the Table_array box, type Bonus In the Col_index_num box, type 2 click on OK. The benefit of using the VLOOKUP function comes when you delete, add, or change the information within your lookup table. You won’t need to change the formulas because they’ll be managed automatically.
Social Plugin