EXCEL PDF training Vlookup function


Télécharger EXCEL PDF training Vlookup function

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


How to use Vlookup in MS Excel in your Wholesale

Discount Database

What is Vlookup?

 

You can use the VLOOKUP function

 

to search the first column of a range of cells,

                                         

and then return a value from any cell on the same row of the range.

 

The V in VLOOKUP stands for vertical

 

Use VLOOKUP instead of HLOOKUP when your comparison values

are located in a column to the left of the data that you want to find.

                                          

Syntax: VLOOKUP(lookup_value, table_array, col_index_num,

[range_lookup])

 

The VLOOKUP function syntax has the following arguments:

 

Lookup value: The value to search in the first column of the table or range

 

Table array: The range of cells that contains the data

                                         

Col index num: The column number in the table array argument

 

from which the matching

value must be returned.

 

Range lookup: A logical value that specifies whether you want

VLOOKUP

 

to find an exact match or an approximate match

 

When searching number or date values,  

 

ensure that the data in the first column of table_array is not stored as text values.

 

In this case, VLOOKUP might return an incorrect or unexpected value.

 

When searching text values in the first column of table_array, 

 

ensure that the data in the first

column of table_array does not contain leading spaces, 

 

trailing spaces, inconsistent use

of straight ( ' or " ) and curly ( ‘ or “) quotation marks, 

 

or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value.

 

Let’s begin!

 

Open your MS Excel

                                         

Click on it

 

This is MS Excel

 

I    will now input my wholesale data

 

Type Wholesale discount

                                                                      

Type quantity ordered

                                                                      



Place the quantities for Wholesale

                                                                      

Type the item code

                                                                          

Input the item codes

 

Put price

 

 

Type discount

 

Type net price

                                                                          

And type total

 

Type discount schedule

                                         

Place quantity

                                         

Input the numbers

                                         

Place discount

                                         

Type the discounts

                                         

Now lets use the VLOOKUP

 

Click this cell

 

Type equals sign =

                                                                         

Type Vlookup

 

Type open parenthesis (

 

Click the first quantity ordered on the first column

                                         

This is the lookup value

                                         

Type comma ,

 

Highlight the whole discount schedule area

 

This is the table array

 

Lock it down by clicking Fn+f4

                                          

Type comma ,



 

Type 2

 

2 is the 2nd column (discount) in the table array

 

Type close parenthesis )

 

Press enter

                                         

Now you have the recommended discount rate

 

Click on the cell

 

Place your mouse at the end of the highlighted cell

 

Till your cursor turned into a plus sign +

 

And then double click

 

And now you have the total list of discount rates per quantity ordered

 

Lets find the total price of each quantity ordered

 

Click this cell

 

Type equals sign =

 

Click 50 (first quantity ordered)

 

Type asterisk *

 

Then click 25.00 (the first price)

 

Press enter

 

The total price will now appear but still without the discount

 

Click on the cell

Place your mouse at the end of the highlighted cell

Till your cursor turned into a plus sign +

 

And then double click

You now have the total list of prices sans the discount

Lets find the final discounted prices

 

Click this cell

 

Type equals sign =

 

Click the first total price on the right

 

Type asterisk *

 

Type open parenthesis (

 

Type 100%

 

Type minus sign -

 

Click the discount

 

Type close parenthesis )

 

Click enter 

 

Click on the cell

 

Place your mouse at the end of the highlighted cell

Till your cursor turned into a plus sign +

 

And then double click

 

You now have the total list of discounted prices

Highlight the 2 columns

Go up the menu bar

                                         

Click the dropdown menu

                                                                          

And choose accounting

 

Now you have the currency on each price

Congratulations, you’re now a pro!

 

 

Credits to Microsoft office and Danny

Rocks of Youtube



2