How do you use vlookup in spreadsheet?

Answer 1

VLOOKUP(Lookup Value,Lookup Table,Column Index)

It looks in the 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.

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.