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, VLookup(value, table_array, table_array, index_number, index_number, not_exact_m no t_exact_match atch ) f irst column of the table_array . value is the value to search for in the first 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 n ot 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, f ound, then the VLookup function will return #N/A. For example:
Provided Provi ded by b y Abacus Computer Training, Rickmansworth Rickmansworth T: 01923 01923 7105 710552 52
PDF Creator - PDF4Free v2.0
M: 0788 07885 5 232 232 030 030
E:
[email protected]
http://www.pdf4free.com
Based on the Excel spreadsheet spreadsheet above: =VLo =VLook okup up((1025 10251, 1, A1:B A1:B2 21, 2, FALSE ALSE)) =VLo =VLook okup up((1025 10251, 1, A1:C A1:C21 21,, 3, FALS FALSE) E) =VL =VLook ookup( up(1024 10248, 8, A1: A1:B21, B21, 2, FALSE ALSE)) =VLo =VLook okup up((1024 10248, 8, A1:B A1:B2 21, 2, TRU TRUE)
woul would d retu return rn "Tof "Tofu" u" woul would d retu return rn $18. $18.60 60 woul ould retur eturn n #N/A #N/A woul would d retu return rn "Q "Que ues so Cabr Cabral ales es""
You will often find it easier using VLOOKUP VLOOKUP if you define range names names first. For example in the above spreadsheet it would work better if you highlighted columns and to D and called it something meaningful meaningful like PRODUCTS. PRODUCTS. The VLOOKUP to find the unit unit price would then be:=VLOOKUP(value ,PRODUCTS,3,False) ,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 path name into the formula). You could type the value 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 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 o rder ID in cell B1 the formula you would enter would be:
=VLOOKUP(B1,[Orders.xls]Sheet1!products,3,False) The spreadsheet name name goes between square square brackets. This is followed followed by the Sheet name and an exclamation exclamation mark. Next comes the range range name followed by a comma, the index 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. on ly.
Provided Provi ded by b y Abacus Computer Training, Rickmansworth Rickmansworth T: 01923 01923 7105 710552 52
PDF Creator - PDF4Free v2.0
M: 0788 07885 5 232 232 030 030
E:
[email protected]
http://www.pdf4free.com
