Formulas & Functions Excel 2007 - 9. Working with Lookup Functions (1 người xem)

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part II: HARNESSING THE POWER OF FUNCTIONS - Tận dụng sức mạnh của các hàm

• Chapter 6: Understanding Functions - Tìm hiểu các hàm
  
  
• Chapter 7: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản
  
  
• Chapter 8: Working with Logical and Information Functions - Làm việc với các hàm luận lý và tra cứu thông tin
  
  
• Chapter 9: Working with Lookup Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11284 - Làm việc với các hàm tìm kiếm
  
  
• Chapter 10: Working with Date and Time Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11365 - Làm việc với các hàm ngày tháng và thời gian
  
  
• Chapter 11: Working with Math Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11551 - Làm việc với các hàm toán học
  
  
• Chapter 12: Working with Statistical Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11623 - Làm việc với các hàm thống kê

---------------------------------------------------------------------------------------


PART II - HARNESSING THE POWER OF FUNCTIONS
Phần II - Tận dụng sức mạnh của các hàm


Chapter 9 - WORKING WITH LOOKUP FUNCTIONS
Chương 9 - Làm việc với các hàm tìm kiếm

Getting the meaning of a word in the dictionary is always a two-step process: First you look up the word itself and then you read its definition. This idea of looking something up to retrieve some related information is at the heart of many spreadsheet operations. For example, you saw in Chapter 4, “Creating Advanced Formulas,” that you can add option buttons and list boxes to a worksheet. Unfortunately, these controls return only the number of the item the user has chosen. To find out the actual value of the item, you need to use the returned number to look up the value in a table.
Tìm được một nghĩa của một từ trong từ điển luôn phải qua hai bước: Đầu tiên, bạn phải tra chính từ đó, và sau đó đọc định nghĩa của nó. Ý tưởng này cũng áp dụng cho việc dò tìm một thứ gì đó để tìm ra một số thông tin liên quan, là trung tâm của nhiều hoạt động bảng tính. Ví dụ, bạn đã thấy ở chương 4, "Thiết lập những công thức (nâng cao)", bạn có thể thêm các nút tùy chọn và các hộp danh sách vào trong một bảng tính; nhưng thật không may, những công cụ điều khiển này chỉ trả về số của mục mà người dùng đã chọn. Để tìm ra giá trị thật của mục đó là gì, bạn cần sử dụng con số được trả về đó để tìm kiếm giá trị trong một bảng.

In many worksheet formulas, the value of one argument often depends on the value of another. Here are some examples:
Trong nhiều công thức của bảng tính, giá trị của một đối số thường phụ thuộc vào giá trị của một đối số khác. Sau đây là một số ví dụ:

• In a formula that calculates an invoice total, the customer’s discount might depend on the number of units purchased.
  Trong một công thức tính tổng giá trị hóa đơn, khoản chiết khấu cho khách hàng phụ thuộc vào số lượng hàng đã mua.
  
  
• In a formula that charges interest on overdue accounts, the interest percentage might depend on the number of days each invoice is overdue.
  Trong một công thức tính lãi suất trên các khoản phải trả quá hạn, số phần trăm lãi suất phụ thuộc vào số ngày quá hạn của mỗi hóa đơn.
  
  
• In a formula that calculates employee bonuses as a percentage of salary, the percentage might depend on how much the employee improved upon the given budget.
  Trong một công thức tính tiền thưởng cho nhân viên theo phần trăm tiền lương, số phần trăm này phụ thuộc vào việc nhân viên này đã đạt được bao nhiêu doanh thu so với chỉ tiêu được giao.

The usual way to handle these kinds of problems is to look up the appropriate value. This chapter introduces you to a number of functions that enable you to perform lookup operations in your worksheet models. Table 9.1 lists Excel’s lookup functions.
Cách thông thường để giải quyết những vấn đề trên là tìm kiếm những giá trị thích hợp. Chương này giới thiệu cho bạn một số hàm dùng để thực hiện những hoạt động dò tìm trong các mô hình bảng tính. Bảng 9.1 liệt kê một số hàm dò tìm của Excel.


Table 9.1 - Excel’s Lookup Functions
Bảng 9.1 - Một số hàm dò tìm trong Excel

• CHOOSEhttp://www.giaiphapexcel.com/forum/showpost.php?p=48023&postcount=2 (num, value1 [, value2, ...]) : Uses num to select one of the list of arguments given by value1, value2, and so on
  Dựa vào num để chọn ra một giá trị từ danh sách của các đối số value1, value2, v.v...
  
  
• GETPIVOTDATA (data_field, pivot_table, field1, item1 [, field2, item2,...]) : Extracts data from a PivotTable
  Trích xuất dữ từ một PivotTable.
  
  
• HLOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=48558&postcount=4 (lookup_value, table_array, row_index_num [, range_lookup]) : Searches for lookup_value in table_array and returns the value in the specified row_index_num
  Dò tìm giá trị lookup_value trong mảng table_array và trả về giá trị ở hàng row_index_num đã được chỉ định trước
  
  
• INDEXhttp://www.giaiphapexcel.com/forum/showpost.php?p=48591&postcount=6 (reference, row_num , column_num [, area_num]) : Looks in reference and returns the value of the cell at the intersection of row_num and column_num
  Tìm trong mảng reference và trả về giá trị tại phần giao nhau của hàng row_num và cột column_num
  
  
• MATCHhttp://www.giaiphapexcel.com/forum/showpost.php?p=48585&postcount=5 (lookup_value, lookup_array [, match_type]) : Searches lookup_array for lookup_value and, if found, returns the relative position of lookup_array in lookup_array
  Tìm giá trị lookup_value trong mảng lookup_array và, nếu ìm thấy, trả về vị trí của lookup_array trong lookup_array
  
  
• VLOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=48554&postcount=3 (lookup_value, table_array, col_index_num [, range_lookup]) : Searches for lookup_value in table_array and returns the value in the specified col_index_num
  Dò tìm giá trị lookup_value trong mảng table_array và trả về giá trị ở cột col_index_num đã được chỉ định trước

Danh sách trên còn thiếu nhiều hàm dò tìm khác, các bạn tra cứu thêm ở đây: Các hàm dò tìm và tham chiếu​

You can download the workbook that contains this chapter’s examples here:
Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây:

www.mcfedries.com/Excel2007Formulas/​

Trong chương này, do hầu hết các hàm tôi đã trình bày chi tiết ở topic: Các hàm dò tìm và tham chiếu, nên tôi sẽ không trình bày lại cú pháp và chú giải các đối số của hàm nữa (không theo như nguyên bản cuốn sách này). Trong các bài dịch sau đây, khi nói đến một hàm nào, tôi sẽ tạo liên kết (link) đến bài viết về hàm đó. Nếu muốn tìm hiểu kỹ hơn về cú pháp và cách sử dụng các đối số (argument), các bạn theo những liên kết này để xem.

 

[BNTT]

Chapter 9 - WORKING WITH LOOKUP FUNCTIONS


9.1. Understanding Lookup Tables
Tìm hiểu về các Bảng Dò Tìm

The table — more properly referred to as a lookup table — is the key to performing lookup operations in Excel. The most straightforward lookup table structure is one that consists of two columns (or two rows):
Bảng (Table) — hay chính xác hơn là Bảng Dò Tìm (Lookup Table) — là chìa khóa để thực hiện những hoạt động dò tìm trong Excel. Cấu trúc bảng dò tìm đơn giản nhất là cấu trúc gồm hai cột:

• Lookup column — This column contains the values that you look up. For example, if you were constructing a lookup table for a dictionary, this column would contain the words.
  Cột dò tìm — Cột này chứa những giá trị để bạn tìm. Ví dụ, nếu bạn tạo một bảng dò tìm cho một cuốn từ điển, thì cột này chứa các từ.
  
  
• Data column — This column contains the data associated with each lookup value. In the dictionary example, this column would contain the definitions.
  Cột dữ liệu — Cột này chứa dữ liệu tương ứng với mỗi giá trị dò tìm. Trong ví dụ tạo từ điển, cột này chứa các định nghĩa cho mỗi từ.

In most lookup operations, you supply a value that the function locates in the designated lookup column. It then retrieves the corresponding value in the data column.
Trong hầu hết các hoạt động dò tìm, bạn cung cấp một giá trị mà hàm sẽ định vị nó trong cột tìm kiếm đã được chỉ định; rồi hàm sẽ truy tìm giá trị tương ứng với nó trong cột dữ liệu.

As you’ll see in this chapter, there are many variations on the lookup table theme. The lookup table can be one of these:
Như bạn sẽ thấy trong chương này, có rất nhiều biến thể về chủ đề bảng dò tìm. Bảng dò tìm có thể là một trong những cái sau đây:

• A single column (or a single row). In this case, the lookup operation consists of finding the nth value in the column.
  Một cột đơn (hoặc một hàng đơn). Trong trường hợp này, việc dò tìm là tìm giá trị thứ n trong cột (hoặc trong hàng).
  
  
• A range with multiple data columns. For example, in the dictionary example, you might have a second column for each word’s part of speech (noun, verb, and so on), and perhaps a third column for its pronunciation. In this case, the lookup operation must also specify which of the data columns contains the value required.
  Một dãy có nhiều cột dữ liệu. Ví dụ, trong việc làm từ điển, bạn có thể có một cột thứ hai cho từ loại (danh từ, động từ, ...), và có lẽ cột thứ ba là cách phát âm của từ đó. Trong trường hợp này, việc dò tìm là phải xác định cột dữ liệu nào trong các cột dữ liệu chứa giá trị được yêu cầu.
  
  
• An array. In this case, the table doesn’t exist on a worksheet but is either an array of literal values or the result of a function that returns an array. The lookup operation finds a particular position within the array and returns the data value at that position.
  Một mảng. Trong trường hợp này, bảng không hiện hữu trong một bảng tính nhưng nó là một mảng các giá trị ký tự hay là kết quả của một hàm nào đó trả về kết quả là một mảng. Việc dò tìm là tìm kiếm một vị trí cụ thể trong mảng và trả về giá trị dữ liệu tại vị trí đó.

 

[BNTT]

Chapter 9 - WORKING WITH LOOKUP FUNCTIONS


9.2. The CHOOSE Function — Hàm CHOOSE

The simplest of the lookup functions is CHOOSE(), which enables you to select a value from a list. Specifically, given an integer n, CHOOSE() returns the nth item from the list. Here’s the function’s syntax:
Hàm dò tìm đơn giản nhất là hàm CHOOSE(), cho phép bạn chọn một giá trị từ một danh sách. Cụ thể, với số nguyên n cho trước, CHOOSE() trả về giá trị thứ n trong danh sách. Cú pháp của hàm như sau:

CHOOSE (num, value1 [, value2, ...])

For example, consider the following formula:
Ví dụ, xem công thức sau đây:

=CHOOSE(2, "Surface Mail", "Air Mail", "Courier")

The num argument is 2, so CHOOSE() returns the second value in the list, which is the string value Air Mail.
Đối số num là 2, do đó CHOOSE() trả về giá trị thứ hai trong danh sách, là chuỗi Air Mail.

If you use range references as the list of values, CHOOSE() returns the entire range as the result. For example, consider the following:
Nếu bạn dùng những tham chiếu đến dãy như là danh sách các giá trị, CHOOSE() trả về toàn bộ dãy đó ở kết quả. Ví dụ, xem công thức sau:

=CHOOSE(1, A11, A22, A33)

This function returns the range A11. This enables you to perform conditional operations on a set of ranges, where the condition is the lookup value used by CHOOSE(). For example, the following formula returns the sum of the range A11:
Hàm trả về dãy A11. Điều này cho phép bạn thực hiện những tác vụ có điều kiện trên một nhóm dãy, với điều kiện là giá trị tìm được bởi hàm CHOOSE(). Ví dụ, công thức sau đây trả về tổng của dãy A11:

=SUM(CHOOSE(1, A11, A22, A33))​

 

[BNTT]

9.2. The CHOOSE Function


Determining the Name of the Day of the Week
Xác định tên của ngày trong tuần

As you’ll see in Chapter 10, “Working with Date and Time Functions,” Excel’s WEEKDAY() function returns a number that corresponds to the day of the week, in which Sunday is 1, Monday is 2, and so on.
Như bạn sẽ thấy ở chương 10, "Làm việc với các hàm ngày tháng và thời gian", hàm WEEKDAY() của Excel trả về một con số tương ứng với một ngày trong tuần: Sunday là 1, Monday là 2, v.v...

What if you want to know the actual day (not the number) of the week? If you need only to display the day of the week, you can format the cell as dddd. If you need to use the day of the week as a string value in a formula, you need a way to convert the WEEKDAY() result into the appropriate string. Fortunately, the CHOOSE() function makes this process easy. For example, suppose that cell B5 contains a date. You can find the day of the week it represents with the following formula:
Phải làm gì nếu bạn muốn biết tên thực tế của ngày trong tuần (chứ không phải là con số)? Nếu bạn chỉ muốn hiển thị ngày trong tuần, bạn có thể định dạng cho ô là dddd. Nếu bạn muốn sử dụng ngày trong tuần như là một giá trị chuỗi trong một công thức, bạn cần một cách để chuyển kết quả của hàm WEEKDAY() thành một chuỗi thích hợp. Thật may, hàm CHOOSE() làm được điều này một cách dễ dàng. Ví dụ, giả sử rằng ô B5 chứa một giá trị ngày. Bạn có thể biết ngày đó là thứ mấy trong tuần với công thức sau đây:

=CHOOSE(WEEKDAY(B5), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

I’ve used abbreviated day names to save space, but you’re free to use any form of the day names that suits your purposes.
Tôi dùng tên tắt của thứ để tiết kiệm chỗ, nhưng bạn cứ việc dùng bất cứ kiểu nào để phù hợp với công việc của bạn.

Here’s a similar formula for returning the name of the month, given the integer month number returned by the MONTH() function:
Sau đây là một công thức tương tự trả về tên của tháng, với con số chỉ tháng do hàm MONTH() trả về:

=CHOOSE(MONTH(date), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")​

Xem thêm: Hàm WEEKDAY(), Hàm MONTH()​

 

[BNTT]

9.2. The CHOOSE Function


Determining the Month of the Fiscal Year
Xác định Tháng của Năm Tài Chính

For many businesses, the fiscal year does not coincide with the calendar year. For example, the fiscal year might run from April 1 to March 31. In this case, month 1 of the fiscal year is April, month 2 is May, and so on. To see how you’d set this up, first consider the following table, which compares the calendar month and the fiscal month for a fiscal year beginning April 1:
Trong nhiều doanh nghiệp, năm tài chính không trùng khớp với năm dương lịch. Ví dụ, năm tài chính có thể bắt đầu vào ngày 1 thàng Tư cho đến ngày 31 tháng Ba (năm sau). Trong trường hợp này, tháng 1 của năm tài chính là tháng Tư, tháng 2 là tháng Năm, v.v... Để xem bạn sẽ thiết lập điều này như thế nào, trước tiên bạn nghiên cứu bảng sau đây, là bảng so sánh tháng của năm dương lịch với tháng của năm tài chính bắt đầu từ ngày 1 tháng Tư:

You need to use the calendar month as the lookup value, and the fiscal months as the data values. Here’s the result:
Bạn cần dùng tháng của năm dương lịch (Calendar Month)như là giá trị tìm kiếm, và tháng của năm tài chính như những giá trị dữ liệu. Đây là kết quả:

=CHOOSE(CalendarMonth, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9)

Figure 9.1 shows an example.
Hình 9.1 minh họa một ví dụ:

Figure 9.1 - Lookup Functions.xlsx​

Calculating Weighted Questionnaire Results
Tính toán kết quả cho những câu hỏi có trọng số

One common use for CHOOSE() is to calculate weighted questionnaire responses. For example, suppose that you just completed a survey in which the respondents had to enter a value between 1 and 5 for each question. Some questions and answers are more important than others, so each question is assigned a set of weights. You use these weighted responses for your data. How do you assign the weights? The easiest way is to set up a CHOOSE() function for each question. For instance, suppose that question 1 uses the following weights for answers 1 through 5: 1.5, 2.3, 1.0, 1.8, and 0.5. If so, the following formula can be used to derive the weighted response:
Một công dụng nữa của hàm CHOOSE() là tính toán kết quả cho những câu hỏi có trọng số (trong một bảng câu hỏi). Ví dụ, giả sử rằng bạn vừa hoàn thành một cuộc khảo sát, trong đó những người trả lời phải nhập một giá trị từ 1 đến 5 cho mỗi câu hỏi. (Bởi vì) có một số câu hỏi và câu trả lời thì quan trọng hơn những câu hỏi và câu trả lời khác, nên mỗi câu hỏi được gán một tập hợp trọng số. (Nghĩa là có nhiều đáp án cho một câu hỏi, và mỗi đáp án thì có mức điểm khác nhau, đáp án nào quan trọng hơn thì có số điểm cao hơn - Loại câu hỏi như vậy thường được gọi là câu hỏi có trọng số) Và bạn dùng những đáp án có trọng số này cho dữ liệu của bạn. Bạn gán các trọng số đó (vào đáp án) như thế nào? Cách dễ nhất là thiết lập hàm CHOOSE() cho mỗi câu hỏi. Ví dụ, nếu như câu hỏi số 1 sử dụng các trọng số sau đây cho mỗi đáp án từ 1 đến 5: 1.5, 2.3, 1.0, 1.8, và 0.5, thì công thức này có thể được sử dụng để lấy ra trọng số của những đáp án:

=CHOOSE(Answer1, 1.5, 2.3, 1.0, 1.8, 0.5)

(Assume that the answer for question 1 is in a cell named Answer1.)
(Giả sử rằng đáp án cho câu 1 nằm trong một ô đã được đặt tên là Answer1)​

 

[BNTT]

9.2. The CHOOSE Function


Integrating CHOOSE() and Worksheet Option Buttons
Tích hợp CHOOSE() với những nút tùy chọn trong bảng tính

The CHOOSE() function is ideal for lookup situations in which you have a small number of data values and you have a formula or function that generates sequential integer values beginning with 1. A good example of this is the use of worksheet option buttons that I mentioned at the beginning of this chapter. The option buttons in a group return integer values in the linked cell: 1 if the first option is clicked, 2 if the second option is clicked, and so on. Therefore, you can use the value in the linked cell as the lookup value in the CHOOSE() function. Figure 9.2 shows a worksheet that does this.
Hàm CHOOSE() rất lý tưởng cho những tình huống dò tìm mà trong đó bạn có một ít giá trị dữ liệu, và bạn có một công thức hoặc một hàm mà kết quả trả về của nó là những số nguyên bắt đầu bằng 1. Một ví dụ điển hình cho chuyện này là dùng những nút tùy chọn (option button) trong một nhóm sẽ trả về các giá trị số nguyên trong ô liên kết: là 1 nếu tùy chọn đầu tiên được chọn, là 2 nếu tùy chọn thứ hai được chọn, v.v... Từ đó, bạn có thể sử dụng giá trị trong các ô liên kết này làm giá trị tìm kiếm của hàm CHOOSE(). Hình 9.2 minh họa một bảng tính thực hiện điều này.

Figure 9.2 - Lookup Functions.xlsx​

The Freight Options group presents three option buttons: Surface Mail, Air Mail, and Courier. The number of the currently activated option is shown in the linked cell, C9. A weight, in pounds, is entered into cell E4. Given the linked cell and the weight, cell E7 calculates the shipping cost by using CHOOSE() to select a formula that multiplies the weight by a constant:
Nhóm Freight Options chứa 3 nút tùy chọn: Surface Mail, Air Mail, và Courier. Con số của tùy chọn đang được kích hoạt hiển thị trong ô liên kết (ô C9). Một giá trị chỉ trọng lượng tính bằng pound nhập trong ô E4. Với những giá trị trong ô liên kết và ô trọng lượng này, ô E7 tính toán chi phí vận chuyển bằng cách sử dụng hàm CHOOSE() để chọn một (trong ba) công thức lấy trọng lượng nhân với một hằng số:

=CHOOSE(C9, E4 * 5, E4 * 10, E4 * 20)​

 

[BNTT]

Chapter 9 - WORKING WITH LOOKUP FUNCTIONS


9.3. Looking Up Values in Tables
Tìm kiếm những giá trị trong các bảng

As you’ve seen, the CHOOSE() function is a handy and useful addition to your formula toolkit, and it’s a function you’ll turn to quite often if you build a lot of worksheet models. However, CHOOSE() does have its drawbacks:
Như bạn đã thấy, hàm CHOOSE() là một sự bổ dung tiện lợi và hữu dung cho bộ công thức của bạn, và nó là một hàm mà bạn sẽ dùng thường xuyên nếu bạn xây dựng nhiều mô hình bảng tính. Tuy nhiên, CHOOSE() có những khuyết điểm như:

• The lookup values must be positive integers.
  Giá trị tìm kiếm phải là những số nguyên.
  
  
• The maximum number of data values is 29.
  Số giá trị dữ liệu chỉ được tối đa là 29.
  
  
• Only one set of data values is allowed per function.
  Trong mỗi hàm chỉ được phép có 1 nhóm giá trị dữ liệu.

You’ll trip over these limitations eventually, and you’ll wonder if Excel has more flexible lookup capabilities. That is, can it use a wider variety of lookup values (negative or real numbers, strings, and so on), and can it accommodate multiple data sets that each can have any number of values? The answer to both questions is “yes”; in fact, Excel has two functions that meet these criteria: VLOOKUP() and HLOOKUP().
Cuối cùng bạn cũng sẽ vượt qua được những giới hạn này, và bạn sẽ tự hỏi rằng Excel có những tính năng dò tìm nào linh hoạt hơn không. Nghĩa là, nó có thể sử dụng nhiều giá trị dò tìm (số âm, số thực, chuỗi, v.v...), và nó có thể chứa nhiều tập hợp giá trị dữ liệu hơn, mà mỗi tập hợp lại có thể có bất kỳ con số hay giá trị nào? Câu trả lời cho cả hai câu hỏi trên là: "Có"; vì thực tế, Excel có hai hàm đáp ứng được những tiêu chuẩn này: VLOOKUP() và HLOOKUP().​

The VLOOKUP Function — Hàm VLOOKUP

The VLOOKUP() function works by looking in the first column of a table for the value you specify. (The V in VLOOKUP() stands for vertical.) It then looks across the appropriate number of columns (which you specify) and returns whatever value it finds there.
Hàm VLOOKUP() tìm kiếm giá trị mà bạn chỉ định trong cột đầu tiên của một bảng (chữ V trong VLOOKUP là viết tắt của chữ Vertical). Rồi nó dò tìm ngang qua một số cột thích hợp (mà bạn cũng đã chỉ định trước), và trả về bất kỳ giá trị nào nó tìm thấy ở đó.

Here’s the full syntax for VLOOKUP():
Đây là cú pháp đầy đủ của hàm VLOOKUP():

VLOOKUP (lookup_value, table_array, col_index_num [, range_lookup])

And here are some notes to keep in mind when you work with VLOOKUP():
Và sau đây là một số điều cần ghi nhớ khi bạn làm việc với hàm VLOOKUP():

• If range_lookup is TRUE or omitted, you must sort the values in the first column in ascending order.
  Nếu đối số range_lookup là TRUE hoặc được bỏ qua, bạn phải sắp xếp các giá trị trong cột đầu tiên theo thứ tự tăng dần.
  
  
• If the first column of the table is text, you can use the standard wildcard characters in the lookup_value argument (use ? to substitute for individual characters; use * to substitute for multiple characters).
  Nếu dữ liệu trong cột đầu tiên của bảng là các giá trị text, bạn có thể dùng những ký tự đại diện trong đối số lookup_value (dùng dấu ? để thay thế cho những ký tự riêng lẻ; dùng dấu * để thay thế cho nhiều ký tự). (Nói thêm: khi ký tự bạn cần tìm kiếm chính là dấu ? hay dấu * này, bạn gõ thêm một dấu ~ vào trước chúng.)
  
  
• If lookup_value is less than any value in the lookup column, VLOOKUP() returns the #N/A error value.
  Nếu lookup_value nhỏ hơn bất kỳ giá trị nào trong cột tìm kiếm, VLOOKUP() trả về giá trị lỗi #N/A.
  
  
• If VLOOKUP() doesn’t find a match in the lookup column, it returns #N/A.
  Nếu VLOOKUP() không tìm được một mục tương ứng trong cột tìm kiếm, nó trả về #N/A.
  
  
• If col_index_num is less than 1, VLOOKUP() returns #VALUE!; if col_index_num is greater than the number of columns in table, VLOOKUP() returns #REF!
  Nếu col_index_num nhỏ hơn 1, VLOOKUP() trả về #VALUE!; nếu col_index_num lớn hơn số cột trong bảng, VLOOKUP() trả về #REF!

The HLOOKUP Function — Hàm HLOOKUP

The HLOOKUP() function is similar to VLOOKUP(), except that it searches for the lookup value in the first row of a table. (The H in HLOOKUP() stands for horizontal.) If successful, this function then looks down the specified number of rows and returns the value it finds there.
Hàm HLOOKUP() tương tự như VLOOKUP(), ngoại trừ việc nó tìm giá trị cần tìm trong hàng đầu tiên của một bảng (chữ H trong HLOOKUP() là viết tắt của chữ Horizontal); nếu thành công, nó tìm xuống một số hàng đã được chỉ định trước, và trả về giá trị mà nó tìm thấy ở đó.

Here’s the syntax for HLOOKUP():
Đây là cú pháp của hàm HLOOKUP():

HLOOKUP (lookup_value, table_array, row_index_num [, range_lookup])​

 

[BNTT]

9.3. Looking Up Values in Tables


9.3.1. Returning a Customer Discount Rate with a Range Lookup
Dò tìm trong dãy để tính một tỷ lệ chiết khấu cho khách hàng

The most common use for VLOOKUP() and HLOOKUP() is to look for a match that falls within a range of values. This section and the next one take you through a few of examples of this range-lookup technique.
Công dụng thông thường nhất của VLOOKUP() và HLOOKUP() là tìm một giá trị tương ứng ở bên trong một dãy giá trị. Phần này và phần tiếp theo sẽ trình bày một vài ví dụ về kỹ thuật dò tìm trong dãy.

In business-to-business transactions, the cost of an item is often calculated as a percentage of the retail price. For example, a publisher might sell books to a bookstore at half the suggested list price. The percentage that the seller takes off the list price for the buyer is called the discount. Often, the size of the discount is a function of the number of units ordered. For example, ordering 1–3 items might result in a 20% discount, ordering 4–24 items might result in a 40% discount, and so on.
Trong các giao dịch giữa các doanh nghiệp, chi phí của một mặt hàng thường được tính dưới dạng một tỷ lệ phần trăm của giá bán lẻ. Ví dụ, một nhà xuất bản có thể bán sách cho một hiệu sách với giá bằng nửa giá được đề nghị trên bảng giá. Phần trăm mà người bán được hưởng từ bảng giá bán lẻ gọi là chiết khấu. Thông thường, khoản chiếu khấu này lớn hay nhỏ tùy thuộc vào số lượng đặt hàng. Ví dụ, đặt từ 1 đến 3 món hàng có thể được chiết khấu 20%, đặt từ 4 đến 24 món hàng có thể được chiết khấu đến 40%, v.v...

Figure 9.3 shows a worksheet that uses VLOOKUP() to determine the discount a customer gets on an order, based on the number of units purchased.
Hình 9.3 minh họa một bảng tính dùng hàm VLOOKUP() để xác định khoản chiết khấu trên một đơn đặt hàng cho một khách hàng, dựa vào số lượng hàng đã mua.

Figure 9.3 - Lookup Functions.xlsx​

For example, cell D4 uses the following formula:
Ví dụ, ô D4 dùng công thức sau:

=VLOOKUP(A4, $H$5:$I$11, 2)

The range_lookup argument is omitted, which means VLOOKUP() searches for the largest value that is less than or equal to the lookup value; in this case, this is the value in cell A4. Cell A4 contains the number of units purchased (20, in this case), and the range $H$5:$I$11 is the discount schedule table. VLOOKUP() searches down the first column (H5:H11) for the largest value that is less than or equal to 20. The first such cell is H6 (because the value in H7 — 24 — is larger than 20). VLOOKUP() therefore moves to the second column (because we specified col_num to be 2) of the table (cell I6) and grabs the value there (40%).
Đối số range_lookup được bỏ qua, nghĩa là VLOOKUP() sẽ tìm giá trị lớn nhất mà nhỏ hơn hoặc bằng với giá trị dò tìm; trong trường hợp này, đó là giá trị trong ô A4. Ô A4 chứa số lượng hàng đã mua (ở đây là 20), và dãy $H$5:$I$11 là các mức chiết khấu tương ứng. VLOOKUP() tìm từ trên xuống giá trị lớn nhất mà nhỏ hơn 20 ở trong cột đầu tiên (H5:H11). Ô đầu tiên nó tìm được là ô H6 (bởi vì giá trị trong H7 — 24 — lớn hơn 20). Rồi VLOOKUP() di chuyển qua cột thứ hai (bởi vì chúng ta đã chỉ định col_num là 2) của bảng (tới ô I6), và lấy giá trị ở đó (40%).

TIP: As mentioned earlier in this section, both VLOOKUP() and HLOOKUP() return #N/A if no match is found in the lookup range. If you would prefer to return a friendlier or more useful message, use the IFERROR() function to test whether the lookup will fail. Here’s the general idea:
Như đã nói ở phần trước, cả VLOOKUP() và HLOOKUP() trả về #N/A nếu nó không tìm thấy giá trị tương ứng trong dãy dò tìm. Nếu bạn thích kết quả trả về là một thông báo thân thiện và hữu dụng hơn (là #N/A), bạn dùng hàm IFERROR() để thử xem việc dò tìm có thất bại hay không. Đây là một công thức chung:

=IFERROR(LookupExpression), "LookupValue not found")

Here, LookupExpression is the VLOOKUP() or HLOOKUP() function, and LookupValue is the same as the lookup_value argument used in VLOOKUP() or HLOOKUP(). If IFERROR() detects an error, the formula returns the “LookupValue not found” string; otherwise, it runs the lookup normally.
Ở đây, LookupExpression là hàm VLOOKUP() hoặc HLOOKUP(), và LookupValue tương đương với đối số lookup_value sử dụng trong hàm VLOOKUP() hoặc HLOOKUP(). Nếu IFERROR() tìm ra một lỗi, công thức trả về chuỗi “LookupValue not found”, còn không, nó thực thi việc dò tìm bình thường.

 

[BNTT]

9.3. Looking Up Values in Tables


9.3.2. Returning a Tax Rate with a Range Lookup
Dò tìm trong dãy để tính một mức thuế suất

Tax rates are perfect candidates for a range lookup because a given rate always applies to any income that is greater than some minimum amount and less than or equal to some maximum amount. For example, a rate of 25% might be applied to annual incomes over $28,400 and less than or equal to $68,800. Figure 9.4 shows a worksheet that uses VLOOKUP() to return the marginal tax rate given a specified income.
Dùng dò tìm trong dãy để xác định các mức thuế suất thì hoàn toàn thích hợp bởi vì một mức thuế đã cho luôn áp dụng cho bất kỳ một mức thu nhập nào có trị giá lớn hơn một con số tối thiểu nào đó và nhỏ hơn hoặc bằng một con số tối đa nào đó. Ví dụ, một mức thuế 25% sẽ áp dụng cho mức thu nhập lớn hơn $28,400 và nhỏ hơn hoặc bằng $68,800. Hình 9.4 minh họa một bảng tính dùng hàm VLOOKUP() để trả về mức thuế suất của một mức thu nhập đã xác định trước.

Figure 9.4 - Lookup Functions.xlsx​

The lookup table is C9:F14, and the lookup value is cell B16, which contains the annual income. VLOOKUP() finds in column C the largest income that is less than or equal to the value in B16, which is $30,000. In this case, the matching value is $28,400 in cell C11. VLOOKUP() then looks in column F to get the marginal rate in row 11, which, in this case, is 25%.
Bảng dò tìm là C9:F14, và giá trị để dò tìm là ô B16, chứa mức thu nhập hằng năm. VLOOKUP() tìm trong cột C mức thu nhập lớn nhất mà nhỏ hơn hay bằng giá trị ở B16 ($30,000). Trong trường hợp này, giá trị tương ứng là $28,400 ở ô C11. Rồi VLOOKUP() tìm ở cột F để lấy mức thuế suất ở hàng 11, là 25%, trong trường hợp này.

TIP: You might find that you have multiple lookup tables in your model. For example, you might have multiple tax rate tables that apply to different types of taxpayers (single versus married, for example). You can use the IF() function to choose which lookup table is used in a lookup formula. Here’s the general formula:
Bạn cũng có thể tìm kiếm khi bạn có nhiều bảng dò tìm trong mô hình của bạn. Ví dụ, bạn có nhiều bảng mức thuế áp dụng cho những đối tượng nộp thuế khác nhau (chẳng hạn người độc thân thì khác với người đã có gia đình). Bạn có thể dùng hàm IF() để chọn ra bảng dò tìm trong công thức dò tìm. Đây là một công thức chung:

=VLOOKUP(lookup_value, IF(condition, table1, table2), col_index_num)

If condition returns TRUE, a reference to table1 is returned, and that table is used as the lookup table; otherwise, table2 is used.
Nếu condition là là TRUE, thì trả về tham chiếu là table1, và dùng bảng này làm bảng dò tìm; còn không thì dùng table2.

 

[BNTT]

9.3. Looking Up Values in Tables


9.3.3. Finding Exact Matches
Tìm chính xác một giá trị

In many situations, a range lookup isn’t what you want. This is particularly true in lookup tables that contain a set of unique lookup values that represent discrete values instead of ranges. For example, if you need to look up a customer account number, a part code, or an employee ID, you want to be sure that your formula matches the value exactly. You can perform exact-match lookups with VLOOKUP() and HLOOKUP() by including the range_lookup argument with the value FALSE. The next couple of sections demonstrate this technique.
Trong nhiều tình huống, dò tìm trong dãy không phải là những gì bạn muốn. Điều này đặc biệt đúng trong các bảng dò tìm chứa những giá trị dò tìm duy nhất đại diện cho những giá trị riêng biệt thay vì là các dãy. Ví dụ, nếu bạn cần tìm một mã số khách hàng, một mã linh kiện, hay ID của một nhân viên, bạn muốn bảo đảm rằng công thức của bạn định vị được giá trị chính xác. Bạn có thể thực hiện việc tìm chính xác một giá trị với hàm VLOOKUP() và HLOOKUP(), bằng cách cho đối số range_lookup có giá trị FALSE. Các phần tiếp theo đây sẽ trình bày kỹ thuật này. ​

Looking Up a Customer Account Number
Tìm kiếm một mã số khách hàng

A table of customer account numbers and names is a good example of a lookup table that contains discrete lookup values. In such a case, you want to use VLOOKUP() or HLOOKUP() to find an exact match for an account number you specify, and then return the corresponding account name. Figure 9.5 shows a simple data-entry screen that automatically adds a customer name after the user enters the account number in cell B2.
Một danh mục gồm mã số và tên khách hàng là một ví dụ điển hình cho một bảng dò tìm chứa những giá trị dò tìm riêng biệt. Trong một trường hợp như vậy, bạn muốn dùng VLOOKUP() hoặc HLOOKUP() để tìm chính xác một mã số mà bạn đã chỉ định, và trả về tên (khách hàng) tương ứng với mã số đó. Hình 9.5 minh họa một màn hình nhập liệu đơn giản, tự động điền tên của khách hàng vào sau khi người dùng nhập mã số (của khách hàng đó) trong ô B2.

Figure 9.5 - Lookup Functions.xlsx​

The function that accomplishes this is in cell B4:
Hàm thực hiện điều này trong ô B4:

=VLOOKUP(B2, D3:E15, 2, FALSE)

The value in B2 is looked up in column D, and because the range_lookup argument is set to FALSE, VLOOKUP() searches for an exact match. If it finds one, it returns the text from column E.
Lấy giá trị ở B2 dò tìm trong cột D, và bởi vì đối số range_lookup đã đặt là FALSE, VLOOKUP() sẽ tìm một giá trị chính xác. Nếu nó tìm được, nó trả về chuỗi text trong cột E.​

Combining Exact-Match Lookups with In-Cell Drop-Down Lists
Kết hợp việc tìm kiếm chính xác với danh sách In-Cell Drop-Down (danh sách xổ xuống trong ô)

In Chapter 4, you learned how to use data validation to set up an in-cell drop-down list. Whatever value the user selects from the list is the value that’s stored in the cell. This technique becomes even more powerful when you combine it with exact-match lookups that use the current list selection as the lookup value.
Trong chương 4, bạn đã học cách sử dụng Data-Validation để thiết lập một danh sách xổ xuống trong ô (in-cell drop-down list), khi người dùng chọn một giá trị trong danh sách, giá trị đó sẽ hiển thị trong ô. Kỹ thuật này trở nên mạnh mẽ hơn khi bạn kết hợp nó với việc dò tìm chính xác, sử dụng giá trị đang được chọn trong danh sách làm giá trị dò tìm.

Figure 9.6 shows an example. Cell C9 contains a drop-down list that uses as its source the header values in row 1 (C1:N1). The formula in cell C10 uses HLOOKUP() to perform an exact-match lookup using the currently selected list value from C9:
Hình 9.6 minh họa một ví dụ. Ô C9 chứa một danh sách xổ xuống (drop-down list ) sử dụng nguồn là các giá trị tiêu đề ở hàng 1 (C1:N1). Công thức ở ô C10 dùng hàm HLOOKUP() để thực hiện việc dò tìm chính xác giá trị đang được chọn từ danh sách các giá trị ở ô C9:

=HLOOKUP(C9, C1:N7, 7, FALSE)

Figure 9.6 - Lookup Functions.xlsx​

 

[BNTT]

Chapter 9 - WORKING WITH LOOKUP FUNCTIONS


9.4. Advanced Lookup Operations
Các thao tác dò tìm nâng cao

The basic lookup procedure — looking up a value in a column or row and then returning an offset value — will satisfy most of your needs. However, a few operations require a more sophisticated approach. The rest of this chapter examines these more advanced lookups, most of which make use of two more lookup functions: MATCH() and INDEX().
Thủ tục dò tìm cơ bản — tìm kiếm một giá trị trong một cột hoặc một dòng và trả về một giá trị dịch chuyển (offset value) — đáp ứng hầu hết các nhu cầu của bạn. Tuy nhiên, có một vài thao tác đòi hỏi phải có một kỹ thuật tinh vi hơn. Phần còn lại của chương này xem xét những thao tác dò tìm nâng cao, mà hầu hết sử dụng hai hàm tìm kiếm này: MATCH() và INDEX(). ​

The MATCH and INDEX Functions — Hàm MATCH và Hàm INDEX

The MATCH() function looks through a row or column of cells for a value. If MATCH() finds a match, it returns the relative position of the match in the row or column. Here’s the syntax:
Hàm MATCH() tìm kiếm trong một hàng hoặc một cột để tìm ra một giá trị. Nếu MATCH() tìm được giá trị tương ứng, nó trả về vị trí của giá trị tương ứng này trong hàng hoặc cột đó. Đây là cú pháp:

MATCH (lookup_value, lookup_array [, match_type])

You can use the usual wildcard characters within the lookup_value argument (provided that match_type is 0 and lookup_value is text). You can use the question mark (?) for single characters and the asterisk (*) for multiple characters.
Bạn có thể dùng những ký tự đại diện trong đối số lookup_value (miễn là match_type là 0 và lookup_value là text). Bạn có thể dùng dấu ? đại diện cho một ký tự và dấu * đại diện cho nhiều ký tự.​

Normally, you don’t use the MATCH() function by itself; you combine it with the INDEX() function. INDEX() returns the value of a cell at the intersection of a row and column inside a reference. Here’s the syntax for INDEX():
Thường thì bạn không dùng hàm MATCH() một mình, bạn thường kết hợp nó với hàm INDEX(). Hàm INDEX() trả về giá trị của ô tại vị trí giao nhau của một hàng và một cột bên trong một tham chiếu. Đây là cú pháp của INDEX():

INDEX (reference, row_num [, column_num] [, area_num])

The idea is that you use MATCH() to get row_num or column_num (depending on how your table is laid out), and then use INDEX() to return the value you need.
Bạn sử dụng MATCH() để xác định row_num hoặc column_num (tùy thuộc vào bảng được trình bày như thế nào), và rồi dùng INDEX() để trả về giá trị bạn cần.

To give you the flavor of using these two functions, let’s duplicate our earlier effort of looking up a customer name, given the account number. Figure 9.7 shows the result.
Để hiểu được cách sử dụng hai hàm này, bạn hãy quay lại với ví dụ dò tìm một tên khách hàng bằng mã số đã cho trước. Hình 9.7 minh họa kết quả.

Figure 9.7 - Lookup Functions.xlsx​

In particular, notice the new formula in cell B4:
Chú ý công thức trong ô B4:

=INDEX(D3:E15, MATCH(B2, D315, 0), 2)

The MATCH() function looks up the value in cell B2 in the range D315. That value is then used as the row_num argument for the INDEX() function. That value is 1 in the example, so the INDEX() function reduces to this:
Hàm MATCH() tìm lấy giá trị ở ô B2 dò tìm trong dãy D315. Rồi giá trị này được dùng làm đối số row_num của hàm INDEX(). Vì trong ví dụ này, giá trị đó bằng 1, nên hàm INDEX() được rút ngắn lại như sau:

=INDEX(D3:E15, 1, 2)

This returns the value in the first row and the second column of the range D3:E15.
Công thức này trả về giá trị tại hàng thứ nhất và cột thứ hai của dãy D3:E15.
​

 

[BNTT]

9.4. Advanced Lookup Operations


9.4.1. Looking Up a Value Using Worksheet List Boxes
Sử dụng các hộp danh sách (List Box) để dò tìm một giá trị

If you use a worksheet list box or combo box as explained in Chapter 4, the linked cell contains the number of the selected item, not the item itself. Figure 9.8 shows a worksheet with a list box and a drop-down list. The list used by both controls is the range A3:A10. Notice that the linked cells (E3 and E10) display the number of the list selection, not the selection itself.
Khi bạn sử dụng một List Box hay một Combo Box của bảng tính, như đã giải thích ở chương 4, ô liên kết chứa số mục được chọn chứ không phải nội dung của mục đó. Hình 9.8 minh họa một bảng tính với một List Box và một Drop-down List (hộp danh sách xổ xuống). Danh sách được sử dụng cho cả hai công cụ điều khiển này là dãy A3:A10. Chú ý rằng các ô liên kết (E3 và E10) hiển thị số của mục được chọn, chứ không phải là nội dung của mục được chọn.

Figure 9.8 - Lookup Functions.xlsx​

To get the selected list item, you can use the INDEX() function with the following modified syntax:
Để có được nội dung của mục được chọn trong danh sách, bạn có thể dùng hàm INDEX() với cú pháp được chỉnh sửa sau đây:

INDEX (list_range, list_selection)

list range : The range used in the list box or drop-down list
Dãy được dùng làm danh sách các mục chọn trong List Box hoặc Drop-down List

list_selection : The number of the item selected in the list
Số của mục được chọn trong danh sách​

For example, to find the item selected from the list box in Figure 9.8, you use the following formula:
Ví dụ, để tìm (tên, nội dung của) mục được chọn trong List Box ở hình 9.8, bạn dùng công thức sau:

=INDEX(A3:A10, E3)
​

 

[BNTT]

9.4. Advanced Lookup Operations


9.4.2. Using Any Column as the Lookup Column
Sử dụng bất kỳ cột nào làm cột dò tìm

One of the major disadvantages of the VLOOKUP() function is that you must use the table’s leftmost column as the lookup column. (HLOOKUP() suffers from a similar problem: It must use the table’s topmost row as the lookup row.) This isn’t a problem if you remember to structure your lookup table accordingly, but that might not be possible in some cases, particularly if you inherit the data from someone else.
Một trong những khuyết điểm chính của hàm VLOOKUP() là phải sử dụng cột đầu tiên bên trái của bảng làm cột dò tìm (hàm HLOOKUP() cũng gặp vấn đề tương tự: phải sử dụng hàng trên cùng của bảng làm hàng dò tìm). Đây không phải là một vấn đề gì to tát nếu như bạn nhớ tạo cấu trúc cho bảng dò tìm một cách thích hợp; nhưng điều đó có thể không làm được trong một số trường hợp, đặc biệt là khi bạn sử dụng dữ liệu của người khác.

Fortunately, you can use the MATCH() and INDEX() combination to use any table column as the lookup column. For example, consider the parts database shown in Figure 9.9:
May thay, bạn có thể dùng hàm MATCH() và INDEX() để sử dụng bất kỳ cột nào trong bảng để làm cột dò tìm. Ví dụ, xem một cơ sở dữ liệu của các linh kiện được minh họa trong hình 9.9:

Figure 9.9 - Lookup Functions.xlsx​

Column H contains the unique part numbers, so that’s what you want to use as the lookup column. The data you need is the quantity in column C. To accomplish this, you first find the part number (as given by the value in B1) in column H using MATCH():
Cột H chứa mã số duy nhất của các linh kiện, do đó nó là cột mà bạn muốn sử dụng làm cột dò tìm; (nhưng) dữ liệu mà bạn muốn có, là số lượng, lại nằm trong cột C. Để làm được điều này, đầu tiên bạn tìm mã số của linh kiện (được ghi như là một giá trị, ở B1) trong cột H bằng hàm MATCH():

MATCH(B1, H6:H13, 0)

When you know which row contains the part, you plug this result into an INDEX() function that operates only on the column that contains the data you want (column C):
Khi bạn đã biết hàng nào chứa linh kiện này, bạn đưa kết quả trên vào một hàm INDEX(), là một hàm (đã được thiết kế) chỉ làm việc trong cột chứa dữ liệu mà bạn muốn (cột C):

=INDEX(C6:C13, MATCH(B1, H6:H13, 0))​

 

[BNTT]

9.4. Advanced Lookup Operations


9.4.3. Creating Row-and-Column Lookups
Tạo cách dò tìm vừa trong hàng vừa trong cột (Row-and-Column Lookup)

So far, all of the lookups you’ve seen have been one-dimensional, meaning that they searched for a lookup value in a single column or row. However, in many situations, you need a two-dimensional approach. This means that you need to look up a value in a column and a value in a row, and then return the data value at the intersection of the two. I call this a row-and-column lookup.
Cho đến bây giờ, tất cả những cách dò tìm mà bạn đã thấy chỉ là dò tìm một chiều, nghĩa là chúng tìm kiếm một giá trị trong một cột đơn hoặc trong một hàng đơn. Tuy nhiên, trong nhiều tình huống, bạn cần có một phương pháp hai chiều. Nghĩa là bạn cần tìm kiếp một giá trị trong một cột và một giá trị trong một hàng, và rồi trả về giá trị dữ liệu tại nơi giao nhau của chúng. Tôi gọi phương pháp này là row-and-column lookup (dò tìm vừa trong hàng vừa trong cột).

You do this by using two MATCH() functions: one to calculate the INDEX() function’s row_num argument, and the other to calculate the INDEX() function’s column_num argument. Figure 9.10 shows an example.
Bạn thực hiện điều này bằng cách dùng hai hàm MATCH(): một cái dùng để tính toán đối số row_num của hàm INDEX(), và cái kia tính toán đối số column_num của hàm INDEX(). Hình 9.10 minh họa một ví dụ:

Figure 9.10 - Lookup Functions.xlsx​

The idea here is to use both the part numbers (column H) and the field names (row 6) to return specific values from the parts database.
Ở đây, ta vừa sử dụng những mã số của các linh kiện (ở cột H), vừa sử dụng tên của các trường (field name - hàng 6), để trả về giá trị cụ thể của nó đó từ cơ sở dữ liệu các linh kiện.

The part number is entered in cell B1, and getting the corresponding row in the parts table is no different than what you did in the previous section:
Mã số của linh kiện được nhập ở ô B1, và việc tìm ra hàng tương ứng (chứa mã số này) trong bảng linh kiện thì không khác gì cách bạn đã làm ở bài trước:

MATCH(B1, H7:H14, 0)

The field name is entered in cell B2. Getting the corresponding column number requires the following MATCH() expression:
Tên trường (field name) được nhập ở ô B2. Để có cột tương ứng (chứa tên trường này), dùng biểu thức MATCH() sau đây:

MATCH(B2, A6:H6, 0)

These provide the INDEX() function’s row_num and column_num arguments (see cell B3):
Những biểu thức trên cung cấp các đối số row_num và column_num cho hàm INDEX() (xem ô B3):

=INDEX(A7:H14, MATCH(B1, H7:H14, 0), MATCH(B2, A6:H6, 0))​

 

[BNTT]

9.4. Advanced Lookup Operations


9.4.4. Creating Multiple-Column Lookups
Tạo những cách dò tìm trong nhiều cột (Multiple-Column Lookup)

Sometimes it’s not enough to look up a value in a single column. For example, in a list of employee names, you might need to look up both the first name and the last name if they’re in separate fields. One way to handle this is to create a new field that concatenates all the lookup values into a single item. However, it’s possible to do this without going to the trouble of creating a new concatenated field.
Đôi khi, dò tìm một giá trị trong một cột đơn thì chưa đủ. Ví dụ, trong một danh sách tên các nhân viên, có thể bạn cần phải tìm cả tên và cả họ nếu chúng nằm trong các cột (trường) riêng biệt. Một cách để giải quyết điều này là tạo thêm một cột mới (cột phụ), ghép tất cả các giá trị cần dò tìm lại thành một mục đơn. Tuy nhiên, có thể làm điều này mà không phải mất công tạo một cột mới.

The secret is to perform the concatenation within the MATCH() function, as in this generic expression:
Bí quyết ở đây là thực hiện việc ghép bên trong hàm MATCH(), như trong biểu thức chung sau đây:

MATCH (value1 & value2, array1 & array2, match_type)

Here, value1 and value2 are the lookup values you want to work with, and array1 and array2 are the lookup columns. You can then plug the results into an array formula that uses INDEX() to get the needed data:
Ở đây, value1 và value2 là những giá trị tìm kiếm mà bạn muốn làm việc với chúng, còn array1 và array2 là những cột tìm kiếm. Rồi sau đó bạn có thể đưa các kết quả vào trong một công thức mảng sử dụng hàm INDEX() để có được dữ liệu cần thiết:

{=INDEX(reference, MATCH(value1 & value2, array1 & array2, match_type))}

For example, Figure 9.11 shows a database of employees, with separate fields for the first name, last name, title, and more.
Ví dụ, hình 9.11 minh họa một cơ sở dữ liệu của nhân viên, với các cột (trường) riêng biệt cho họ, tên, chức vụ, v.v...:

Figure 9.11 - Lookup Functions.xlsx​

The lookup values are in B1 (first name) and B2 (last name), and the lookup columns are A6:A14 (the First Name field) and B6:B14 (the Last Name field). Here’s the MATCH() function that looks up the required column:
Những giá trị cần tìm kiếm nẳm ở B1 (first name) và B2 (last name), và những cột dùng để tìm là A6:A14 (cột First Name) và B6:B14 (cột Last Name). Đây là hàm MATCH() dùng để dò tìm ra các cột theo yêu cầu:

MATCH(B1 & B2, A6:A14 & B6:B14, 0)

We want the specified employee’s title, so the INDEX() function looks in C6:C14 (the Title field). Here’s the array formula in cell B3:
Chúng ta muốn tìm chức vụ (title) của một nhân viên đã được xác định, nên hàm INDEX() tìm kiếm trong C6:C14 (cột Title). Đây là công thức mảng ở ô B3:

{=INDEX(C6:C14, MATCH(B1 & B2, A6:A14 & B6:B14, 0))}​

---------------------------------------------------------------------
Trong bài này, có chữ field, nếu dịch là trường dữ liệu thì chính xác, nhưng có lẽ hơi khó nghe, do đó tôi tạm dịch field là cột, tuy không chính xác, nhưng phù hợp với ví dụ thực tế trong bài. ​

---------------------------------------------------- End of Chapter 9 ----------------------------------------------------​