How do you use vlookup in excel?

Answer 1

A vLookup is a vertical lookup function. You have a similar function called a hLookup or horizontal lookup. Bot allow you to set up a table of data and then search either vertically or horizontally through it to find particular values. That will depend on what way your data is laid out.

For example, you could have a table of 10 different products being sold in 10 different shops. Each shop charges a different price. You could arrange these in a table, say with the names of the shops across the top and the products down the side and with the correct prices in the correct cells. Then in another part of the spreadsheet, you could type in the name of one of the shops in one cell and the name of the product in another. The vlookup or hlookup functions can go to your table, find the name of the shop and the name of the product and display the correct price for you. This would be a lot quicker than trying to find the price yourself.

Answer 2

There are 3 lookup functions. VLookup, HLookup and Lookup.

VLOOKUP(Lookup Value,Lookup Table,Column Index)

Looks in Lookup Table for a column whose first column contains the Lookup Value and then moves down the column according to Column Index and returns the value of the cell. The values in the first column of Lookup Table can be text, numbers, or logical values. They must be placed in ascending order: 0, 1, 2, ...; A-Z; FALSE, TRUE. If it can't find lookup value, it uses the largest value that is less than the lookup value.

The three parts of it are:

Lookup Value: A value to search for.

Lookup Table: The range of values to search through and return a value from.

Column Index: The number of the column of the value to be returned.

The corresponding function HLOOKUP is used when the values are arranged Horizontally rather than vertically.

Set up a table as follows with the following values in the following cells:

C1: 0

D1: 10%

C2: 250

D2: 25%

C3: 1000

D3: 30%

C4: 5000

D4: 48%

For the Vlookup, in the the cell A6 put the following formula: =A5*VLOOKUP(A5,C1:D5,2)

Lookup Value: A5 which contains the value 10

Lookup Table: C1:D5 Values in Column C are the values to be searched and those in D are returned.

Column Index: 2. The second Column in the range, in this case Column D.

It takes the value in A5 which is 10 and checks the values in column C for a match. In this case it does not find an exact match. The largest value lower than it is the 0 in cell C1. It then moves to the corresponding value in D1 which is 10%. This is then multiplied by A5 giving the value 1 in cell A6. If A5 is changed to contain 1150 it would multiply A5 by the 30% as 1000 in column C would be the highest value lower than 1150 and its corresponding value in Column D is 30% as 1000 is in C3 and 30% is in D3.

For the HLookup you need to arrange the figures in a different way:

A1: 0

A2: 10%

B1: 250

B2: 25%

C1: 1000

C2: 30%

D1: 5000

D2: 48%

In this case the formula in A5 will be =A4*HLOOKUP(A4,A1:E2,2)

The third function is the Lookup Function.

Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.

The LOOKUP function has two syntax forms: vector and array.

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values you want to match. The other form of LOOKUP automatically looks in the first column or row.

Syntax 1 - Vector form

LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

Lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text is equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

Examples

In a sheet put the following values into the following cells:

A2: 4.14

A3: 4.19

A4: 5.17

A5: 5.77

A6: 6.38

A7: 7.3142

B2: Red

B3: Orange

B4: Yellow

B5: Green

B6: Blue

B7: Violet

In separate cells the following formulas will give the following results.

=LOOKUP(4.91,A2:A7,B2:B7) equals "orange"

=LOOKUP(5.00,A2:A7,B2:B7) equals "orange"

=LOOKUP(7.66,A2:A7,B2:B7) equals "violet"

=LOOKUP(7.66E-14,A2:A7,B2:B7) equals #N/A, because 7.66E-14 is less than the smallest value in the lookup_vector A2:A7

The lookup functions allow you to look for a value in a list of values and return it or another value depending on what is found. There are a number of types of lookup functions. See the related question below.

Answer 3

The LOOKUP function allows you to search for a value in a list, or the closest value that is less than or equal to what it is searching for. You need one search value and at least one row or column to search in. If you have two or more columns or rows, it will search for your search value in the first row or column and return a value from the last row or column. There are other related functions called VLOOKUP and HLOOKUP.

Answer 4

Code for Performing a VLOOKUP Function in Visual Basic for Applications (VBA) by Structure Learning Assistance - SLA

Function VLOOKUP(lookupValue As Variant, tableArray As Range, colIndexNum As Integer, _

Optional rangeLookup As Boolean = True) As Variant
'Declare variables
Dim result As Variant
'Perform the VLOOKUP function
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, colIndexNum, rangeLookup)
'Return the result
VLOOKUP = result

End Function

This code defines a custom VBA function that performs a VLOOKUP just like the VLOOKUP formula in Excel. To use this function in your code, you would call it just like any other VBA function, passing the necessary parameters (lookupValue, tableArray, and colIndexNum) and setting rangeLookup to either True (approximate match) or False (exact match) as desired. The function returns the result of the VLOOKUP.

Contact Us:

SLA Consultants India

82-83, 3rd Floor, Vijay Block,

Above Titan Eye Shop,

Metro Pillar No. 52,

Laxmi Nagar,New Delhi,110092

Call +91- 8700575874

Answer 5

You use a vlookup function to reference a table. An example is:

=VLOOKUP(I4,$I$20:$J$24, 2)