Tutor HuntResources Microsoft Excel Resources
Part 2 Vlookup
The VLOOKUP Function
Date : 10/02/2021
Author Information
Uploaded by : Nick
Uploaded on : 10/02/2021
Subject : Microsoft Excel
In this section I examine the fundamentals of VLOOKUP.
So what is VLOOKUP? Well it is a way of searching a table for information based on a reference value and then returning the content from a specified column. We can use this to search a reference table for example a list of clients in a database or we can use it to auto populate a form based on a reference. The key aspect here is that there needs to be a unique reference identifier and the column containing the identifier needs to be the first column. Let s see what this looks like when written out: =VLOOKUP(P1,A1:N5000,5,0) The =VLOOKUP tells the programme what function you are using All functions are enclosed in brackets () P1 is the lookup value (this is what the programme will seek out from the table. In this case it is a cell reference but could easily be some text or a figure The A1:N5000 is the table array or range where the programme will look. Column 1 of this range needs to contain the lookup value The 5 is the column number that the programme will look to when returning a value 0 indicates that the programme should return a value when an exact match is made. If 1 was indicated then an approximate match can be made. You must be careful when using this as incorrect values can be returned when you use 1As with most functions you can substitute elements for references to other cells. In this case the lookup value has been referenced to a cell. The table array is a relative reference but could be named or absolute. The column number can also be replaced with a reference or calculation but be aware that if the column number exceeds the number of columns in the table array then an error message will appear. The final element approximate (1) or Exact (0) cannot be changed or the cell must contain either of the two options.want to know more? Feel free to drop me a line.
So what is VLOOKUP? Well it is a way of searching a table for information based on a reference value and then returning the content from a specified column. We can use this to search a reference table for example a list of clients in a database or we can use it to auto populate a form based on a reference. The key aspect here is that there needs to be a unique reference identifier and the column containing the identifier needs to be the first column. Let s see what this looks like when written out: =VLOOKUP(P1,A1:N5000,5,0) The =VLOOKUP tells the programme what function you are using All functions are enclosed in brackets () P1 is the lookup value (this is what the programme will seek out from the table. In this case it is a cell reference but could easily be some text or a figure The A1:N5000 is the table array or range where the programme will look. Column 1 of this range needs to contain the lookup value The 5 is the column number that the programme will look to when returning a value 0 indicates that the programme should return a value when an exact match is made. If 1 was indicated then an approximate match can be made. You must be careful when using this as incorrect values can be returned when you use 1As with most functions you can substitute elements for references to other cells. In this case the lookup value has been referenced to a cell. The table array is a relative reference but could be named or absolute. The column number can also be replaced with a reference or calculation but be aware that if the column number exceeds the number of columns in the table array then an error message will appear. The final element approximate (1) or Exact (0) cannot be changed or the cell must contain either of the two options.want to know more? Feel free to drop me a line.
This resource was uploaded by: Nick
Other articles by this author
- Conditional Formatting - An Introduction
- Excel Introduction
- Excel Power Query - Merging and Appending Queries
- Part 1 IF
- Part 3 INDEX and MATCH