Excel VLOOKUP Function Tutorial

By -
The following example will give you an idea on how to use VLOOKUP in Microsoft Excel. If you're working with an excel database and want it to be dynamic the VLOOKUP/HLOOKUP functions are very useful for you. The VLOOKUP function looks up value in columns while HLOOKUP does in rows.


 This example has two sheets "Price_List" and "Orders" for mobile brands.
*Note: The values given are not the actual market prices.


The "Price_List" sheet has three columns ID, Model and Price. This is where we'll be looking up the prices value for every orders made.


Now, on the orders tab we have 3 orders made. We'll add the VLOOKUP function in the Total Price formula. This formula "=VLOOKUP(B2,Price_List!B1:C7,2,FALSE)" will LOOKUP for the price of the unit brand in the Price_List Sheet.

=IF(C2<>"",VLOOKUP(B2,Price_List!B1:C7,2,FALSE) *C2,VLOOKUP(B2,Price_List!B1:C7,2,FALSE))
The formula above is a logical condition to calculate the Total Price when the quantity is entered.
The condition is:
If the quantity "C2" is blank then it will just lookup the unit price from the Price_List Sheet. And if the quantity is entered it will calculate by multiplying the unit price with the quantity value.

Comments

Post a Comment

Popular posts from this blog

How to Create a Configuration.INI Files in VB6

By -
Image
In this example shows only a simple form that saves and retrieves value from the CONFIG.INI file. After reading this, you can use your own creativity on how you're going to implement this on your project. To start with, On your Form add the following controls and arrange them according to the image above. (1) TextBox - Change the Name property to "txtSetting" (2) Command Button - Change the Caption and Name Properties to the following:                                        Save:          Change the Name property to "cmdSave"                                        Retrieve:    Change the Name property to "cmdRetrieve" (5) Label - For the two labels we'll be using to retrieve the values. Remove the default caption and change the Name Properties to lblName and lblTime . Now, Copy the Form Codes below and Paste on the Form1 code window of your VB6 Project. Form Codes: Option Explicit Dim ConfigFile As String Private Sub cm
Read More »

How to Make Windows Form Transparent using Visual Basic 6/VB6

By -
The following code snippets allows you to make windows form transparent using VB6. In a standard module, copy and paste the following declarations and function. [ Module1.bas ] Option Explicit Const LWA_COLORKEY = 1 Const LWA_ALPHA = 2 Const LWA_BOTH = 3 Const WS_EX_LAYERED = &H80000 Const GWL_EXSTYLE = -20 Private Declare Function SetLayeredWindowAttributes Lib "user32" (ByVal hWnd As Long, ByVal color As Long, ByVal x As Byte, ByVal alpha As Long) As Boolean Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAP
Read More »

How to Set Windows Form Always on Top of Other Applications in VB6

By -
The following code snippets allows to set Windows Form Always on Top of Other applications in Visual Basic 6.0. Public Const SWP_NOMOVE = 2 Public Const SWP_NOSIZE = 1 Public Const FLAGS = SWP_NOMOVE Or SWP_NOSIZE Public Const HWND_TOPMOST = -1 Public Const HWND_NOTOPMOST = -2 Declare Function SetWindowPos Lib "user32" _ (ByVal hWnd As Long, _ ByVal hWndInsertAfter As Long, _ ByVal x As Long, _ ByVal y As Long, _ ByVal cx As Long, _ ByVal cy As Long, _ ByVal wFlags As Long) As Long The following SetTopMostWindow() function takes two parameters as follows:       hWnd : the window handle       Topmost : It is either True to set the on top or False if Not. Public Function SetTopMostWindow(hWnd As Long, Topmost As Boolean) As Long If Topmost = True Then 'Make the window topmost SetTopMostWindow = SetWindowPos(hWnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS) Else SetTopMostWindow = SetWindowPos(hWnd, HWND_NOTOPM
Read More »