Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
programming4us
Windows 7

Microsoft Excel : How to Use the VLookUp Function

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
3/27/2015 2:21:47 AM

VLOOKUP is a Microsoft Excel function that will look up a value in a row based on the value searched on in the first column of that row. This function can be used directly in Excel or in a macro or Visual Basic program using VBA (Visual Basic for Applications).



For instance in the following table, using the VLOOKUP function, one could do a search on Jones and retrieve the value 52. It doesn't matter how many columns are in the table or which column the desired value is in, but the search criteria must be in the first column searched using the function. The V stands for vertical as opposed to an HLOOKUP where the search criteria are in a row.


       A         B

1    Adams 75

2   Jones    52

3   Smith    50

The syntax of the VLOOKUP function is as follows:

VLOOKUP(SearchValue, Array, ColumnNumber, [Range_Lookup])


‘SearchValue’ is the value you are looking for in the first column of the spreadsheet or range. I the example above, the ‘SearchValue’ would be ‘Jones.’


‘Array’ refers to the range of cells that contains the data to be searched. In the above example, the ‘Array’ would be ‘A1:B3’.)


‘ColumnNumber’ refers to the column that contains the data that will be returned by the search. In the example, the  ‘ColumnNumber’ would be 2.


‘Range_Lookup is an optional true/false argument that determines whether the search needs to return an exact or approximate value. If this is set to ‘False,’ VLOOKUP will only return an exact match. In this case the values in the first column need to be sorted. If it is set to ‘True’ or omitted, the column must be sorted and the function will return the largest value less than ‘SearchValue.’ In the example, if the ‘SearchValue’ were ‘Johnson’ instead of ‘Jones,’’ the function find a match with ‘Adams’ and return 75.


The complete function for the above example would be =VLOOKUP("Jones", A1:B3, 2)


Including the function in VBA code


To use the VLOOKUP function in VBA it should be wrapped in a sub-routine with declared variables following normal programming practices. Ideally, one would pass all values to the routine as variables so that the routine and the VLOOKUP function itself would be generic and could be re-used.


First, declare the variables to be used in the VLOOKUP function


dim vSearchValue as Variant

dim rArray as Range

dim iColumnNumber as Integer

dim bRangeLookup as Boolean


Next declare the variable used to hold the return on the VLOOKUP function. The contents of this variable will also be used to return from the routine.


dim vReturn as Variant


Putting it all together


Put all the components in a routine and test it against a spreadsheet.


Sub GetValue

dim vSearchValue as Variant

dim rArray as Range

dim iColumnNumber as Integer

dim bRangeLookup as Boolean

dim vReturn as Variant


vReturn = Application.WorksheetFunction.VLookup(vSearchValue, rArray, iColumnNumber, bRangeLookup)


End Sub

Other -----------------
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
- MDT's Client Wizard : Application Properties
- MDT's Client Wizard : Operating System Properties
- MDT's Client Wizard : Customizing the Deployment Share
- Microsoft Access 2010 : Report Properties and Why to Use Them, Basing Reports on Stored Queries or Embedded SQL Statements
- Microsoft Access 2010 : Working with Sorting and Grouping (part 2) - Sorting and Grouping Settings
- Microsoft Access 2010 : Working with Sorting and Grouping (part 1) - Add Sorting and Grouping to a Report
- Microsoft Access 2010 : Building Reports Based on More Than One Table (part 3) - Working with Subreports
- Microsoft Access 2010 : Building Reports Based on More Than One Table (part 2) - Build a One-to-Many Report by Using the Report Wizard
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer