Excel - Vlookup

  • October 2019
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Excel - Vlookup as PDF for free.

More details

  • Words: 483
  • Pages: 2
Using the VLOOKUP Function The VLookup function searches for value in the left-most column of the table_array and returns the value in the same row based on the index_number. The syntax for the VLookup function is: VLookup(value, table_array, index_number, not_exact_match ) value is the value to search for in the first column of the table_array. table_array is two or more columns of data that is sorted in ascending order. index_number is the column number in table_array from which the matching value must be returned. The first column is 1. not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value. Note: If index_number is less than 1, the VLookup function will return #VALUE!. If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!. If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLookup function will return #N/A. For example:

Provided by Abacus Computer Training, Rickmansworth T: 01923 710552

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

http://www.pdf4free.com

Based on the Excel spreadsheet above: =VLookup(10251, A1:B21, 2, FALSE) =VLookup(10251, A1:C21, 3, FALSE) =VLookup(10248, A1:B21, 2, FALSE) =VLookup(10248, A1:B21, 2, TRUE)

would return "Tofu" would return $18.60 would return #N/A would return "Queso Cabrales"

You will often find it easier using VLOOKUP if you define range names first. For example in the above spreadsheet it would work better if you highlighted columns and to D and called it something meaningful like PRODUCTS. The VLOOKUP to find the unit price would then be:=VLOOKUP(value,PRODUCTS,3,False) Value = the Order ID you wish to look up In this example the VLOOKUP formula could be typed into a totally separate workbook (you would need to put the whole path name into the formula). You could type the value you want to look up into a cell on the screen. For example let us say that the above example is called Orders.xls and it is stored on the C: drive in a folder called My Documents

If you were to type the above on a separate worksheet to look up the price of the order ID in cell B1 the formula you would enter would be:

=VLOOKUP(B1,[Orders.xls]Sheet1!products,3,False) The spreadsheet name goes between square brackets. This is followed by the Sheet name and an exclamation mark. Next comes the range name followed by a comma, the index number of the column that contains the price followed by a comma and then the word false which indicates we require an exact match only.

Provided by Abacus Computer Training, Rickmansworth T: 01923 710552

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

http://www.pdf4free.com

Related Documents

Excel - Vlookup
October 2019 10
08 Excel Vlookup Dan Hlookup
November 2019 13
09 Excel Vlookup Dan If
November 2019 11
Vlookup&hlookup.xlsx
April 2020 8
Vlookup Instructions
December 2019 14
Vlookup Error
May 2020 7