Vlookup Function – Advanced with calculations

OVERVIEW OF VLOOKUP FUNCTION IN MICROSOFT EXCEL

VLOOKUP is a type of lookup function in Ms-Excel, there is also another named the HLOOKUP, but I will only focus my points on VLOOKUP.  VLOOKUP is used to search for the first column in a spreadsheet for a value. If the worth is seen, you can replace data of any cell in that row.

It (VLOOKUP function) needs three bits of information, ramified by commas. (You can also add an optional fourth value, which we’ll see in a moment)

Lookup (value_to_lookup, data_to_search, results_column, [match case])

To demonstrate VLOOKUP, produce the following spreadsheet.

vlookup

Notice that the data in the first column, item number, is in ascending order. If you do not sort data, lookups can give you problem.

The item number we want to search for is in cell F1 and is a value of 583. We can do VLOOKUP to return the Gadget type associated with the item number 583.

Click cell F2 to select it, then click the Formula Bar at the top. Enter the following formula: =VLOOKUP (F1, A2: C10, 2)

After you have entered the formula, Press the Enter key on your keyboard. You should find the value of “Sony” is displayed.

The value we want to search for is in F1. The data we want to search for is cell A2 to C10. The column that needs to be returned is column 2. When Excel finds the 583 it seems that this on row 6. The value in row 6, column 2 is what VLOOKUP gives as an answer.

You can also add a Fourth Argument after the string number. This argument is either TRUE or FALSE and has to do with corresponding values from what you are sorting for (the F1 for use). If you enter a value of True then Excel looks for an exact string. If no exact string is found, it settles for the next tropical value. For instance, Enter 580 in cell F1, when you press enter you should see “LG” appear in cell F2. Because we left off a value of true or false as the fourth argument it defaults to true. When Excel can’t find a value of 580 it looks at the next value lower than 580, which is 471 for us. This is on row 5. Row 5, column 2 is “LG”.

Now click cell F2 to highlight it. Click the Formula Bar and better your function to this: =VLOOKUP (F1, A2: C10, 2, FALSE)

Changes made was to add FALSE to the end. Press the Enter key and Excel gives you an N/A error. It does this because FALSE means “find a definite match”. Because we didn’t have a value of 580 an error is returned.

Change FALSE to TRUE and try again. You get LG as an answer.

Now change the 580 in cell F1 to 150. Press Enter, cell F2 shows the N/A error again, this error occurs because 150 is smaller.

Change the value in cell F1 to 612, Hit the enter key and you will find that “HP” appears in cell F2

HOW TO CALCULATE WITH VLOOKUP

Using VLOOKUP function for calculation in Ms-Excel is very tricky. So when making any calculation, you have to be very careful with your methods.

For instance, if we want to calculate the discount price for HP, we can simply make use of VLOOKUP to obtain the price and multiply it by the discount value in the D column or empty column.

Click in cell F3 to select it. Click the Formula Bar and input this code: =VLOOKUP (F1, A2: C10, 3, FALSE).

Press the Enter key, you should find that a value of 40 pounds appears in cell F3. The discount for the gadget type HP is 15% discount is applied.

Click cell F4. Click the formula bar and enter the following:

=VLOOKUP (F1, A2:D10, 3, FALSE) * (1 – VLOOKUP (F1, A2:D10, 4, FALSE))

So we now have the VLOOKUP function two times. The first one gets the value in column 3. This is the price of the gadgets. The second one meets the value from column 4, which is the discount. The result from the initial VLOOKUP (£40) is then multiplied by the product of the second VLOOKUP (15%).

When you press the enter key, a value of 34 should be returned – the price minus the discount.

Was this article helpful?

Related Articles

Leave a Reply