Using VLOOKUP In Excel Dashboard Reports

posted in: Excel, Excel Dashboards | 0

VLOOKUP Explained – Step by Step:

VLOOKUP in Excel: What is VLOOKUP?

The Excel Vlookup function is one of the lookup syntax used when you need to find things in a table in excel. Vlookup looks for a variable in cells and gives results that are in the same row like the value you are searching for. But for this to be effective datasheet must be vertically listed.

Business Management Software


Rated #1 Excel Dashboards, Scorecards and KPIs Reports

Download Free Templates for Sales, HR, Finance, Marketing, Quality...



 

vlookup in excel
Vlookup in Excel Dashboard Example

 

For you to create vlookup syntax, you need to establish the value you want to look up. Then establish the range in which lookup value is located.

At this point make sure that lookup value is always in first column in the range to enable vlookup to work properly. After that establish the column number in the range that has the return value. Lastly you may optionally specify TRUE for near match or FALSE for exact match in return value.

 

The following steps will assist in creating Vlookup in Excel:

1.    A Place for Your Formula

Start by selecting cells where you want vlookup function to go. Then at the Font group of the Home tab click the little arrow next to the Borders button.

After that right click and select Format Cells tab and go to the Border tab. Select a thick border at the Style options then click the Outline preset then horizontal line in the mid. Then select a thinner border line style and click the vertical line in the mid. Then type variables in the cells and make the texts bold.

2.    What are you looking for?

To put things into the function to make it work, you need four inputs. They include Lookup value, Table_array, Col_index_num and [range_lookup]. In vlookup make sure these inputs are separated with a comma.

Once a comma is placed, the tooltip box tells how far you are in the formula by making current part syntax bold. For lookup value you may either type in name or put reference to the cell where you will type in the name. Also you may select a cell or type the cell say F2 in the formula bar then comma to move on in the formula.

3.    Where are you looking?

For vlookup to return wanted information, the vlookup function requires to know the all dataset. Therefore type range and press F4 or Command + T in MAC. Then once the range is locked type comma.

4.    What you want to return

Using vlookup gives you the answer you want called col_index_num in excel or column Index Number. Every column has column index number, therefore returning data from column, you will have to quote the index number.

5.    Do you want to be approximate match or precise?

Approximate match is used when you are looking up for a value close to the value you are searching. The exact match is used to look up for values equal to your lookup value. To pick exact or approximate value just pick FALSE from menu that pops up on entering a comma or type FALSE after comma.

6.    Press enter

After pressing enter you need to validate your formula. To validate and establish if the formula works, you have to enter something for the formula to look up. Once the Value Not Available Error occurs click the small exclamation mark left of the cell. The error value comes up because Excel is looking for nothing. Entering variables like names the error will be eliminated.

Rated #1 Excel Dashboards, Scorecards and KPIs Reports

Here is How to Easily Track your KPIs in Excel

Free Business Management Templates