vlookup from another workbook

generic formula 

Bạn đang xem: VLOOKUP from another workbook

=vlookup (b5,[workbook]sheet!range,4,0)

summary 

to lookup product data, pricing, or other information stored in a separate (external) workbook, you can use the vlookup function with a full reference to the other workbook. in the example shown, the formula in c5 is:

 
=vlookup (b5,'[product data.xlsx]sheet1'!$b$4:$e$12,4,0)

sample data

the data in the external workbook looks like this:

vlookup from another workbook

explanation 

this is a standard use of the vlookup function to retrieve data from the 4th column in a table:

  • lookup value comes from b5
  • table_array is a reference to a range in an external workbook
  • col_index is 4, to retrieve data from the fourth column
  • range_lookup is zero to force an exact match

the only difference is the special syntax used for external references, in the “table_array” argument. the syntax for external references is:

 
  • workbook is the name of the external workbook (i.e. data.xlsx)
  • sheet is the name of the sheet containing the range (i.e. sheet1)
  • range is the actual range for table array (i.e. a1:c100)

the easiest way to enter a reference to an external table, is to begin entering the vlookup function normally. then, when entering the table_array argument, browse to the the external workbook and select the range directly. excel will construct the needed reference automatically.

note: if the workbook with the lookup table is open, vlookup will show the workbook name and address for the table_array argument. if not, vlookup will display the full file path to the workbook + workbook name and address.

handling spaces and punctuation

note the reference to workbook is enclosed in square brackets, and the entire workbook + sheet is enclosed in single quotes. the single quotes are required when the workbook or sheet name contains space or punctuation characters

Xem thêm: Hướng dẫn dùng hàm MID và MIDB trong Excel và Ví dụ cụ thể

related formulas 

in this example, we have a table of employee locations like this on sheet2: on sheet1, we retrieve the building location for each team member using this formula: = vlookup ( b5 , sheet2 ! $b$5:$c$104 , 2 , 0 ) the lookup value is the employee id,…

the gist of this formula is to build up a complete reference to a range in another workbook as text, then use the indirect function vĩ đạivert the text to an actual reference. a reference to an external worksheet looks like this: ‘[sample data.xlsx]…

this is a standard vlookup exact match formula with one exception: the column index is supplied by the match function. note that the lookup array given to match (b2:e2) representing column headers deliberately includes the empty cell b2. this is…

in this example, the scores in column f are lookup values for the vlookup function. the lookup table is the named range “key” (b5:c9). the column index is provided as 2, since we want vlookup to return a grade from the second column. finally, the…

in this case “id” is a named range = b4 (which contains the lookup value), and “data” is a named range = b8:e107 (the data in the table). the number 3 indicates the 3rd column in the table (last name) and false is supplied to force an exact match…

this is a standard “exact match” vlookup formula with one exception: the column index is calculated using the column function. when the column function is used without any arguments, it returns a number that corresponds to the current column. in…

when vlookup can’t find a value in a lookup table, it returns the #n/a error. the iferror function allows you to catch errors and return your own custom value when there is an error. if vlookup returns a value normally, there is no error and the…

related functions 

vlookup is an excel function to look up data in a table organized vertically. vlookup supports approximate and exact matching, and wildcards (* ?) for partial matches. lookup values must appear in the first

Xem thêm: Hàm chia trong excel – Cách sử dụng và công thức cụ thể

related videos 

in this video learn how to use vlookup to build a simple form to extract employee information from a table in about three minutes. vlookup is a key excel function.

in this video we look at how lớn tưởngfigure vlookup to look up values based on an approximate match. this is good for tax rates, postage, commissions, and the like.

in this video we look at a few reasons why vlookup is a better option than nested if statements.

this video shows how to use vlookup to merge data in two tables based on a common id. you can even use this technique to reorder the columns you retrieve.

excel formula training

formulas are the key to getting things done in excel. in this accelerated training, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with vlookup and index & match, count and sum with criteria, dynamically rank values, and create dynamic ranges. you’ll also learn how to troubleshoot, trace errors, and fix problems. instant access. see details here.

download 100+ important excel functions

get over 100 excel functions you should know in one handy pdf.

excel video training

quick, clean, and to the point.

learn more

Nguồn: https://ngoalongtamquoc.vn
Danh mục: EXCEL