How Do I Create a Vlookup?

Answer 1

=VLOOKUP(value,range,colIndex,true/false)

value = the value you want to find on the the range

range = the range where your value is found AND the value you want returned

column index = the column value you want returned when a match is found. Note: Excel uses 1-index meaning column A = 1, B = 2 and so on.

True/False = FALSE is an exact match, TRUE is an approximate match. You should always use FALSE.

Another note - VLOOKUP will return the first matching value. If you have multiple matching values with different return values, VLOOKUP will return the first one. VLOOKUPs can also only look up values to the left of the found value. So in the below example, if the price was to the right of the product name, VLOOKUP wouldn't work, the column would have to be oriented to the left of the product.

here's an example:

I have Table1 which contains products and stock but not price, and Table2 which contains product and price. Say they look like this.

Table1

A B.

Product. Qty

Apples. 2

Oranges. 5

bananas 6

Table2

A B

Product. Price

Apples. $4.00

Oranges $2.00

bananas. $1.50

return price to Table 1

=VLOOKUP(A2,TABLE2!A2:B3,2,FALSE)

FinalTable

A B. C

Product Qty Price

Apples. 2. $4.00

Oranges. 5. $2.00

bananas 6. $1.50

Answer 2

Below there are some notes on the Vlookup syntax. Once you get used to the syntax it is quite easy to implement. If you have questions please let me know and I would be happy to help you further. I can even show you Index/Match which is a much more powerful lookup function than Vlookup.

'=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value This will be the employee id. We can use a drop down to select different emp ids thereby outputting the appropriate position.

table_array Select the entire table including headers

col_index This is the numeric number for the column that houses the data we want. Col A=1, Col B=2, etc… Col F=6

range_lookup Always make this FALSE when just starting out