Excel Lesson: Part 2

5. VLookUp:

VLookUp helps you find a data relating to the subject you are looking for. For instance, you are looking at the salary data for a particular person. So it is just matching a particular data to a subject.

Formula:

VLOOKUP=(Lookup_value)(Table_array)(Col_index_num)(Range_lookup)

Alternative formula description:

VLOOKUP = (Look Up Subject Location) (Table Array) (Desired Data Column No of Table Array) (Exact or Estimate)

Explanation:

Look Up Subject Location:

• What subject do you want to look for?

Table Array:

• What range of data you intend to look up.
• The first column must be the subject column
• Do not include the title of the columns

Column No of Table Array:

• What column is the data you want to look up on.
• The column number only applies in the table array
• It is counted from left to right, where subject is consider column 1
• It may help if you save and name your Table array data in advance so it will appear clean in the formula

Exact or Estimate:

• For exact match: type ‘FALSE’ or the number ‘0’
• For appropriate match: type ‘TRUE’ or the number ‘1’

Also be aware of the difference between relative cells reference and absolute cell reference.

Relative cell reference: Relative to the position of the formula.

Examples:

Here are types of VLOOKUP

1. Creating VLOOKUP search field (with exact matched value) 

e.g. The search field

It is useful to use droplist in search field to prevent error such as including incidentally including spacing

2. Creating a VLOOKUP search field (with appropriate matched value)

e.g. Search field for a commission, commission rate and

3. Creating VLOOKUP list of drop-down (with exact match)

e.g. invoice

As the formula will be dragged down, it is important to lock the table array. This is done by F4

4. Creating VLOOKUP table (with exact matched value)

e.g. Find the subject’s data such as individual info such as people’s info,  inventory description,

5. Creating VLOOKUP table (with approximate matched value)

e.g. commission payment to sales team

Don’t forget to lock the array table with F4 key

5. Creating VLOOKUP table from another spreadsheet (with exact value)

…

6. Creating VLOOKUP table (with approximate value)

e.g. Comission or Tax table

(e) Replacing error signs:

=IFERROR(value, value if error)

Example:

To make it blank

=IFERROR(VLOOKUP(…), “”)

(f) VLOOKUP with MATCH: 

Match formula can be nested (embedded) under the column no in the VLOOKUP formula.

Dashboard:

Count overdue date: =COUNTIF(Range,”<“&TODAY())

Count expiring date (i.e 30 days before expiry date):

=COUNTIFS(Range,”>=”&TODAY(),Range,”<“&TODAY()+30)

Count unique value: =SUMPRODUCT((Range<>””)/COUNTIF(Range,Range&””))