Excel's Lookup Function - Các Hàm dò tìm và tham chiếu

[BNTT]

List of Excel's Lookup Function


Danh mục các hàm dò tìm và tham chiếu

ADDRESShttp://www.giaiphapexcel.com/forum/showpost.php?p=48936&postcount=10 (row_num, column_num, abs_num, a1, sheet_text) : Tạo địa chỉ ô ở dạng text, theo chỉ số dòng và chỉ số cột được cung cấp

AREAShttp://www.giaiphapexcel.com/forum/showpost.php?p=48942&postcount=11 (reference) : Trả về số vùng tham chiếu trong một tham chiếu. Mỗi vùng tham chiếu có thể là một ô rời rạc hoặc là một dãy ô liên tục trong bảng tính

CHOOSEhttp://www.giaiphapexcel.com/forum/showpost.php?p=48023&postcount=2 (num, value1, value2, ...) : Chọn một giá trị trong một danh sách

COLUMNhttp://www.giaiphapexcel.com/forum/showpost.php?p=48948&postcount=12 (reference) : Trả về số thứ tự cột của ô đầu tiên ở góc trên bên trái của vùng tham chiếu

COLUMNShttp://www.giaiphapexcel.com/forum/showpost.php?p=48948&postcount=12 (reference) : Trả về số cột của vùng tham chiếu

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) : Trả về dữ liệu được lưu giữ trong báo cáo PivotTable. Có thể dùng GETPIVOTDATA để lấy dữ liệu tổng kết từ một báo cáo PivotTable, với điều kiện là phải thấy được dữ liệu tổng kết từ trong báo cáo đó.

HLOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=48558&postcount=4 (lookup_value, table_array, row_index_num, range_lookup) : Dò tìm một cột chứa giá trị cần tìm ở hàng đầu tiên (trên cùng) của một bảng dữ liệu, nếu tìm thấy, sẽ tìm tiếp trong cột này, và sẽ lấy giá trị ở hàng đã chỉ định trước

HYPERLINKhttp://www.giaiphapexcel.com/forum/showpost.php?p=48959&postcount=13 (link_location, friendly_name) : Dùng để tạo một kết nối, một siêu liên kết

INDEXhttp://www.giaiphapexcel.com/forum/showpost.php?p=48591&postcount=6 (reference, row_num, column_num, area_num) : Tìm một giá trị trong một bảng (hoặc một mảng) nếu biết vị trí của nó trong bảng (hoặc mảng) này, dựa vào số thứ tự hàng và số thứ tự cột

INDIRECThttp://www.giaiphapexcel.com/forum/showpost.php?p=49124&postcount=14 (ref_text, a1) : Trả về một tham chiếu từ chuỗi ký tự. Tham chiếu được trả về ngay tức thời để hiển thị nội dung của chúng - Cũng có thể dùng hàm INDIRECT khi muốn thay đổi tham chiếu tới một ô bên trong một công thức mà không cần thay đổi công thức đó

LOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=49611&postcount=15 (lookup_value, lookup_vector, result_vector) : Dạng VECTƠ - Tìm kiếm trên một dòng hoặc một cột, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) được chỉ định

LOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=49611&postcount=15 (lookup_value, array) : Dạng MẢNG - Tìm kiếm trên dòng (hoặc cột) đầu tiên của một mảng giá trị, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) cuối cùng trong mảng đó

MATCHhttp://www.giaiphapexcel.com/forum/showpost.php?p=48585&postcount=5 (lookup_value, lookup_array, match_type) : Trả về vị trí của một giá trị trong một dãy giá trị

OFFSEThttp://www.giaiphapexcel.com/forum/showpost.php?p=49765&postcount=16 (reference, rows, cols, height, width) : Trả về tham chiếu đến một vùng nào đó, bắt đầu từ một ô, hoặc một dãy ô, với một khoảng cách được chỉ định

ROWhttp://www.giaiphapexcel.com/forum/showpost.php?p=49862&postcount=17 (reference) : Trả về số thứ tự dòng của ô đầu tiên ở góc trên bên trái của vùng tham chiếu

ROWShttp://www.giaiphapexcel.com/forum/showpost.php?p=49862&postcount=17 (reference) : Trả về số dòng của vùng tham chiếu

TRANSPOSEhttp://www.giaiphapexcel.com/forum/showpost.php?p=49864&postcount=18 (array) : Chuyển một vùng dữ liệu ngang thàng dọc và ngược lại (luôn được nhập ở dạng công thức mảng)

VLOOKUPhttp://www.giaiphapexcel.com/forum/showpost.php?p=48554&postcount=3 (lookup_value, table_array, col_index_num, range_lookup) : Dò tìm một hàng (row) chứa giá trị cần tìm ở cột đầu tiên (bên trái) của một bảng dữ liệu, nếu tìm thấy, sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị ở cột đã chỉ định trước.
​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm CHOOSE


Hàm CHOOSE

Đây là một trong những hàm dò tìm đơn giản nhất, thường dùng để chọn một giá trị trong một danh sách.

Cú pháp: CHOOSE(index_num, value1, [value2]...)

index_num : Là vị trí của giá trị cần trả về trong danh sách. Ví dụ, index_num = 1 thì lấy giá trị value1; index_num bằng 2 thì lấy giá trị value2... Index_Num phải là một số nguyên (hoặc là một công thức là đáp số là số nguyên), và trong khoảng từ 1 đến 29

value : Danh sách các giá trị để chọn. Tối đa là 29. Những giá trị này có thể là con số, là chuỗi, là một vùng tham chiếu, là một công thức, là một hàm...
​

​

Ví dụ: Công thức = SUM(CHOOSE(a, A11, A22, A33))
Có nghĩa là: Nếu a bằng 1, thì lấy tổng của A11, nếu a bằng 2 thì lấy tổng của A22, nếu a bằng 3 thì lấy tổng của A33
​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm VLOOKUP()


Hàm VLOOKUP

Hàm VLOOKUP() sẽ dò tìm một hàng (row) chứa giá trị mà bạn cần tìm ở cột đầu tiên (bên trái) của một bảng dữ liệu (chữ V trong VLOOKUP có nghĩa là vertical),
nếu tìm thấy, nó sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị ở cột mà bạn đã chỉ định trước.

Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num [, range_lookup])

lookup_value ___Giá trị dùng để tìm kiếm trong cột đầu tiên của table_array, giá trị này có thể là một số, một chuỗi, hoặc là một tham chiếu

table_array____Bảng dùng để dò tìm, có thể là một vùng tham chiếu hoặc là tên (name) của một vùng đã được đặt tên

col_index_num__Số thứ tự của các cột trong table_array, chứa kết quả mà bạn muốn tìm kiếm.
col_index_num__Số thứ tự này được tính từ trái sang phải (cột chứa lookup_value là cột thứ nhất)

range_lookup___Là một giá trị kiểu Borlean, để chỉ kiểu tìm kiếm: chính xác hay tương đối.

- TRUE (hoặc 1 - mặc định): Là kiểu dò tìm tương đối
_____VLOOKUP() sẽ tìm giá trị lookup_value đầu tiên mà nó tìm được trong cột đầu tiên của table_array
_____trong trường hợp không tìm ra, nó sẽ trả về giá trị lớn nhất mà nhỏ hơn lookup_value
- FALSE (hoặc 0): Là kiểu dò tìm chính xác
_____VLOOKUP() sẽ tìm chính xác giá trị lookup_value trong cột đầu tiên của table_array
_____trong trường hợp không có, hoặc lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của table_array, nó sẽ báo lỗi #N/A!
​

Một số lưu ý khi sử dụng hàm VLOOKUP()

- Để có kết quả chính xác khi range_lookup = TRUE, bạn phải sắp xếp các giá trị các giá trị trong cột đầu tiên của table_array từ nhỏ đến lớn.

- Nếu cột đầu tiên của table_array chứa các giá trị kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value
- (dấu * đại diện cho nhiều ký tự / hoặc dấu ? đại diện cho một ký tự)

- Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của table_array, hàm sẽ báo lỗi #N/A!
- Lỗi này thường gặp khi bạn gõ dư một khoảng trắng ở cuối lookup_value

- Nếu không tìm thấy lookup_value khi range_lookup = FALSE, hàm sẽ báo lỗi #N/A!

- Nếu col_index_num nhỏ hơn 1, hàm sẽ báo lỗi #VALUE!, còn nếu col_index_num lớn hơn số cột trong table_array, hàm sẽ báo lỗi #REF!
​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm HLOOKUP()


Hàm HLOOKUP

Hàm HLOOKUP() sẽ dò tìm một cột (column) chứa giá trị mà bạn cần tìm ở hàng đầu tiên (trên cùng) của một bảng dữ liệu (chữ H trong HLOOKUP có nghĩa là horizontal),
nếu tìm thấy, nó sẽ tìm tiếp trong cột này, và sẽ lấy giá trị ở hàng mà bạn đã chỉ định trước.

Cú pháp: HLOOKUP(lookup_value, table_array, row_index_num [, range_lookup])

lookup_value ___Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array, giá trị này có thể là một số, một chuỗi, hoặc là một tham chiếu

table_array____Bảng dùng để dò tìm, có thể là một vùng tham chiếu hoặc là tên (name) của một vùng đã được đặt tên

col_index_num__Số thứ tự của các hàng trong table_array, chứa kết quả mà bạn muốn tìm kiếm.
col_index_num__Số thứ tự này được tính từ trên xuống (hàng chứa lookup_value là hàng thứ nhất)

range_lookup___Là một giá trị kiểu Borlean, để chỉ kiểu tìm kiếm: chính xác hay tương đối.

- TRUE (hoặc 1 - mặc định): Là kiểu dò tìm tương đối.
_____HLOOKUP() sẽ tìm giá trị range_lookup đầu tiên mà nó tìm được trong hàng đầu tiên của table_array
_____trong trường hợp không có, nó sẽ lấy giá trị lớn nhất mà nhỏ hơn range_lookup - FALSE (hoặc 0): Là kiểu dò tìm chính xác
_____HLOOKUP() sẽ tìm chính xác giá trị range_lookup trong hàng đầu tiên của table_array
_____trong trường hợp không có, hoặc range_lookup nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên của table_array, nó sẽ báo lỗi #N/A!
​

* Xem thêm các lưu ý khi sử dụng hàm ở bài trên, hàm VLOOKUP()
​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm MATCH()


Hàm MATCH

Hàm MATCH() dùng để biết vị trí của một giá trị trong một dãy giá trị (một hàng hoặc một cột)

Cú pháp: MATCH(lookup_value, lookup_array, match_type)

lookup_value ___Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array, giá trị này có thể là một số, một chuỗi, hoặc là một tham chiếu

lookup_array___Cột hoặc hàng dùng để dò tìm

match_type____Là 1, 0 hoặc 1, để chỉ kiểu tìm kiếm

-1 : MATCH() sẽ tìm giá trị nhỏ nhất mà lớn hơn hoặc bằng lookup_value
_____trong trường hợp này, lookup_array phải được xếp theo thứ tự giảm dần (từ lớn đến nhỏ) 0 : _MATCH() sẽ tìm chính xác giá trị lookup_value

1 : _(là giá trị mặc định) MATCH() sẽ tìm giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value
_____trong trường hợp này, lookup_array phải được xếp theo thứ tự tăng dần (từ nhỏ đến lớn)
​

Một số lưu ý khi sử dụng hàm MATCH()

- Nếu match_type = 0, và lookup_value là giá trị kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value
- (dấu * đại diện cho nhiều ký tự / hoặc dấu ? đại diện cho một ký tự)

- Nếu không tìm thấy lookup_value trong lookup_array, hàm MATCH() sẽ báo lỗi #NA!
​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm INDEX()


Hàm INDEX

Trả về một giá trị hoặc một tham chiếu tới một giá trị của một bảng (hoặc một mảng).
Hàm INDEX() có hai dạng: dạng MẢNG và dạng THAM CHIẾU. Dạng mảng luôn luôn trả về giá trị hoặc mảng các giá trị; còn dạng tham chiếu luôn luôn trả về kết quả là một tham chiếu.


INDEX DẠNG MẢNG (Array Form)

Cú pháp: = INDEX(array, row_num, column_num)

array : Là một dãy ô hoặc là một hằng mảng.

Nếu array chỉ có một dòng hoặc một cột, thì row_num hay column_num tương ứng là tùy chọn.

Nếu array có nhiều hơn một dòng hoặc nhiều hơn một cột, và chỉ có hoặc là row_num hoặc là column_num được sử dụng, INDEX() sẽ trả về toàn bộ dòng hay cột của array
​

row_num : Dòng trong array để lấy giá trị trả về. Nếu bỏ qua row_num thì buộc phải có column_num.

column_num : Cột trong array để lấy giá trị trả về. Nếu bỏ qua column_num thì buộc phải có row_num.​

Lưu ý:

• Nếu hai đối số column_num và row_num đều sử dụng, INDEX() sẽ trả về giá trị của ô là giao điểm của column_num và row_num.

• Nếu đặt row_num = 0, INDEX() sẽ trả về một mảng các giá trị là toàn bộ cột; và ngược lại, nếu đặt column_num = 0, INDEX() sẽ trả về một mảng các giá trị là toàn bộ dòng.

• Để kết quả trả về là các giá trị dưới dạng mảng, cần nhập INDEX() theo dạng công thức mảng, mảng ngang cho dòng và mảng đứng cho cột.

• column_num và row_num phải chỉ tới một ô trong mảng, nếu không, INDEX() sẽ báo lỗi #REF!

Ví dụ 1:

Ví dụ 2:

INDEX DẠNG THAM CHIẾU (Reference Form)

Cú pháp: = INDEX(reference, row_num, column_num, area_num)

reference : Là một tham chiếu tới một hoặc nhiều dãy ô.

Nếu đang nhập một dãy ô gồm nhiều phần rời rạc cho tham chiếu, cần đặt dãy đó trong cặp dấu ngoặc đơn.

Nếu mỗi vùng trong tham chiếu chỉ có một dòng hoặc một cột, đối số row_num hoặc column_num là tùy chọn, theo thứ tự này. Ví dụ, đối với tham chiếu chỉ gồm một dòng, dùng cú pháp: INDEX(reference, ,column_num)​

row_num : Là chỉ số Dòng trong tham chiếu.

column_num : Là chỉ số Cột trong tham chiếu.

area_num : Chọn một dãy trong tham chiếu để trả về giao điểm của column_num và row_num trong dãy đó. Vùng thứ nhất được đánh số là 1, vùng thứ hai là 2, v.v... Nếu bỏ qua area_num, INDEX sẽ mặc định dùng vùng 1.

Ví dụ, nếu tham chiếu mô tả các ô (A1:B4, D1:E4, G1:H4), thì vùng 1 là dãy A1:B4, vùng 2 là dãy D1:E4, và vùng 3 là dãy G1:H4.​

Lưu ý:

• Sau khi reference và area_num đã chọn một dãy riêng biệt, row_num và column_num đã chọn ô riêng biệt: row_num 1 là dòng thứ nhất trong dãy, column_num 1 là cột thứ nhất trong dãy, v.v... tham chiếu được trả về bởi INDEX() là giao của dòng và cột.

• Nếu đặt row_num = 0, INDEX() sẽ trả về tham chiếu cho toàn bộ cột; và ngược lại, nếu đặt column_num = 0, INDEX() sẽ trả về tham chiếu cho toàn bộ dòng.

• column_num, row_num và area_num phải chỉ tới một ô bên trong tham chiếu, nếu không, INDEX() sẽ báo lỗi #REF! Nếu bỏ qua column_num và row_num, INDEX() sẽ trả về một vùng trong tham chiếu, được chỉ định bởi area_num.

• Kết quả của INDEX() là một tham chiếu, nhưng tùy thuộc vào công thức sử dụng mà giá trị của INDEX() sẽ được sử dụng dưới dạng một tham chiếu hay một giá trị. Ví dụ, công thức CELL("width", INDEX(A1:B2, 1, 2)) thì tương đương với CELL("width", B1): hàm CELL() sử dụng trả về của INDEX như một tham chiếu ô. Nhưng với công thức 2*INDEX(A1:B2, 1, 2) thì giá trị trả về của INDEX() sẽ được dịch thành một giá trị trong ô B1.

Ví dụ:

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Một số ví dụ về Hàm dò tìm và tham chiếu


Tìm tên dựa vào mã số

Chúng ta có bài toán sau:

​

Yêu cầu: nhập mã số vào ô B2, ô B4 sẽ có biết tên, dựa vào danh mục ở D3:E15

Xin đề nghị hai cách giải sau:

1. Dùng hàm VLOOKUP(): B4 = VLOOKUP(B2, D3:E15, 2, FALSE)

__Lấy giá trị ở B2 đi dò với cột đầu tiên của bảng D3:E15, nếu tìm thấy B2 ở hàng nào thì lấy giá trị trên cùng hàng đó trong cột thứ 2 của bảng D3:E15


2. Dùng hàm INDEX() kết hợp với MATCH(): B4 =INDEX(D3:E15, MATCH(B2, D315, 0), 2)

__Lấy giá trị ở B2 đi dò trong khối cell D315, để tìm vị trí của hàng chứa B2 (trong khối cell D315)
__Dò tìm trong bảng D3:E15, lấy giá trị là ô giao nhau giữa hàng vừa tìm được ở trên và cột thứ 2.
​

Tìm tổng doanh thu của một tháng

Bài toán sau yêu cầu: Chọn tháng trong ô E9, ô E10 sẽ cho biết doanh thu trong tháng đó

​

Công thức đề nghị: E10 = HLOOKUP(E9, B1:M7, 7, FALSE)

__Lấy giá trị ở E9 đi dò với hàng đầu tiên của bảng B1:M7, nếu tìm thấy E9 ở cột nào thì lấy giá trị trên cùng cột đó trong hàng thứ 7 của bảng B1:M7​

Dùng bất cứ cột nào để dò tìm theo cột

Bạn xem đề bài này nhé:

​

Yêu cầu là tìm cái Quantity (ở cột C) dựa vào cái Number (ở cột H)
Thoạt nhìn qua, thấy dò tìm theo cột... bạn sẽ nghĩ đến VLOOKUP() ? Nhưng không được, cột H nằm sau cột C !

Tôi nhớ có lần trên diễn đàn này, có bạn đã hỏi có cách nào để VLOOKUP() tìm ngược,
họ muốn cột chứa trị dò tìm không phải là cột đầu tiên bên trái !

Vậy nên phải dùng cách khác, xin gợi ý công thức trong cell B2 nhé, kết hợp INDEX() và MATCH():

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

__Lấy giá trị ở B1 đi dò trong khối cell H6:H13, để tìm vị trí của hàng chứa giá trị B1 (trong H6:H13), ví dụ hàng thứ 5
__Dò tìm trong khối cell C6:C13, lấy giá trị ở hàng thứ 5... (là 75)
​

​

 

[BNTT]

Một số ví dụ về Hàm dò tìm và tham chiếu (tt)


Row-and-Colunm Lookups (dò tìm vừa theo hàng vừa theo cột)

Thường thì chúng ta hay dò tìm dựa theo một cột hoặc một hàng.
Tuy nhiên, trong vài trường hợp, chúng ta phải dò tìm vừa theo một hàng vừa theo một cột, và kết quả là kết hợp của cả hai cái đó.
Người ta gọi trường hợp này là Row-and-Column Lookups.

Để dễ hiểu hơn, các bạn xem ví dụ sau:

Cũng là bảng tính mà tôi đã giới thiệu ở ví dụ Dùng bất cứ cột nào để dò tìm theo cột, nhưng yêu cầu thì khác:

Ở bài trước, người ta chỉ yêu cầu cho biết cái Quantity (cột C) dựa vào Number (cột H),

Còn bài này, người ta muốn biết tất cả những cái trên hàng tiêu đề (Division, Description, Quantity, Cost...) của Number (ở cột H) là gì...

Ví dụ: với Number = D-178, thì Division = 4, Description = Gangley Pliers, Quantity = 57, Cost = $10.47, v.v...

​

Ở ô B1 là một cái Data Validation List (tạm hiểu là danh sách) của H7:H14, và ở ô B2 là một cái Data Validation List của A6:H6

Dựa vào B1 và B2, cho biết B3 ?

Chúng ta có thể dùng hàm INDEX() với thông số của nó là hai hàm MATCH(),
một cái để tìm ra vị trí hàng (của B1 trong H7:H14) và một cái để tìm ra vị trí cột (của B2 trong A6:H6):

tìm vị trí hàng: MATCH(B1, H7:H14, 0)

tìm vị trí cột: MATCH(B2, A6:H6, 0)

và đây là công thức ở ô B3: =INDEX(A7:H14, MATCH(B1, H7:H14, 0), MATCH(B2, A6:H6, 0))
​

 

[BNTT]

Một số ví dụ về Hàm dò tìm và tham chiếu (tt)


Tìm kiếm một giá trị bằng cách dùng ListBox hoặc ComboBox

Tôi có một bảng tính như hình sau:

​

Trong đó tôi có dùng một ListBox, với Input Range của nó là A3:A10 và Cell Link là E3.

Khi tôi chọn một giá trị trong List box, thì ở Cell Link (E3) hiện ra số thứ tự của giá trị đó trong ListBox...

Tôi muốn rằng khi tôi chọn một giá trị trong ListBox này, thì giá trị sẽ hiện ra, chứ không phải là số thứ tự.

Tôi sẽ dùng hàm INDEX(), với cú pháp như sau:

INDEX(list_range, list_selection)

list_range ____Danh sách các giá trị trong Listbox
list_selection _.Số thứ tự của danh sách trong Listbox
​

Trong trường hợp này, để có giá trị hiện ra ở ô E4, tại E4 tôi dùng công thức: =INDEX(A3:A10, E3)
​

Tìm kiếm trong nhiều cột

Đôi khi, các dữ liệu dùng để tìm kiếm không nằm ở một cột mà trong nhiều cột.
Ví dụ, bạn có một danh sách đã được tách sẵn họ và tên riêng ra hai cột.
Người ta yêu cầu dựa vào họ và tên để xác định chức vụ.
Chúng ta có thể dùng một cột phụ để nối họ và tên lại rồi tìm theo cột phụ... Nhưng có lẽ không cần, vì tôi sẽ dùng hàm INDEX() và MATCH() với công thức mảng.

Hàm MATCH() có một cú pháp ít người biết đến, đó là:

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

value1 & value2 là các dữ liệu để tìm (ví dụ họ và tên)
array1 & array2 là các cột (hoặc hàng) chứa các dữ liệu dùng để tìm kiếm đó
​

Và đây là cú pháp dùng INDEX() ghép với MATCH() trong công thức mảng:

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

Ví dụ, các bạn xem hình sau đây:

​

Chúng ta có:
B1 là danh sách các First Name (tên), B2 là danh sách các Last Name (họ)
Vùng chứa Fisrt Name là A6:A14, vùng chứa Last Name là B6:B14 và vùng chứa Title (chức vụ) là C6:C14

Công thức dựa vào First Name (ở B1) và Last Name (ở B2), dò trong vùng chứa First Name (A6:A14) và Last Name (B6:B14) mà xác định ra Title trong (C6:C14):
​

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

 

[BNTT]

Một Số Hàm Khác Về Dò Tìm Và Tham Chiếu


Hàm ADDRESS

Dùng để tạo địa chỉ ở dạng text, theo chỉ số dòng và chỉ số cột được cung cấp.

Cú pháp: =ADDRESS(row_num, column_num [, abs_num] [, a1] [, sheet_text])

row_num: Số thứ tự dòng của địa chỉ
column_num: Số thứ tự cột của địa chỉ
abs_num: Kiểu địa chỉ (tuyệt đối hay tương đối) - mặc định là tuyệt đối

​

a1: Dạng địa chỉ (=TRUE: dạng A1; =FALSE: dạng R1C1) - mặc định là TRUE
sheet_text: Tên của sheet chứa địa chỉ - mặc định là không có

​

Ví dụ

​

 

[BNTT]

Hàm AREAS

Trả về số vùng tham chiếu trong một tham chiếu. Mỗi vùng tham chiếu có thể là một ô rời rạc hoặc là một dãy ô liên tục trong bảng tính.


Cú pháp: =AREAS(reference)

reference: Là một hoặc nhiều vùng tham chiếu mà bạn cần đếm. Nếu muốn đưa nhiều vùng rời rạc nhau vào công thức thì bạn phân cách chúng bằng dấu phẩy, nếu không ngăn bằng dấu phẩy, Excel sẽ không đếm (xem ví dụ 3 dưới đây)
​

Ví dụ:

​

 

[BNTT]

Hàm COLUMN

Trả về số thứ tự cột của ô đầu tiên ở góc trên bên trái của vùng tham chiếu.


Cú pháp: =COLUMN(reference)

reference: Là ô hoặc một vùng nhiều ô. Nếu reference bỏ trống thì COLUMN() trả về số thứ tự cột của ô chứa công thức.
​

Ví dụ:

COLUMN(A10) = 1
COLUMN(Y5:Z6) = 25
​

Hàm COLUMNS

Trả về số cột của vùng tham chiếu.


Cú pháp: =COLUMNS(array)

array: Là ô hoặc một vùng nhiều ô, mảng tham chiếu
​

Ví dụ:

COLUMNS(A10:C30) = 3
​

​

 

[BNTT]

Hàm HYPERLINK

Dùng để tạo một kết nối, một liên kết để mở một tài liệu...


Cú pháp: =HYPERLINK(link_location, friendly_name)

link_location: Đường dẫn đến tài liệu cần mở, nhập ở dạng chuỗi ký tự. Link_location có thể chỉ đến một ô đặc biệt, tên của một dãy ô trong một trang bảng tính hoặc một bảng tính, hoặc chỉ đến một đánh dấu (bookmark) trong Microsoft Excel. Link_location cũng có thể chỉ đến một tập tin lưu trên ổ cứng, hoặc một đường dẫn truy xuất nội bộ trên một máy chủ hoặc một đường dẫn tài nguyên URL trên mạng intranet, internet.

• Link_location có thể là một chuỗi ký tự đặt trong dấu nháy kép, hoặc một ô nào đó chứa đường dẫn dưới dạng chuỗi ký tự.
• Nếu link_location liên kết đến một tài nguyên không tồn tại, sẽ xuất hiện lỗi khi bạn kích vào ô chứa hàm HYPERLINK() này.

firendly_name: Là nội dung hiển thị trong ô chứa hàm HYPERLINK(), có thể là một số, hoặc chuỗi ký tự. Nội dung này sẽ hiển thị bằng màu xanh và có gạch chân, nếu không có firendly_name thì chính link_location sẽ hiển thị.

• Firendly_name có thể là một giá trị, một chuỗi ký tự, một tên mảng, hoặc một ô liên kết đến một giá trị hoặc một chuỗi văn bản.
• Nếu firendly_name liên kết đến một giá trị bị lỗi, thì chính tên cái lỗi đó sẽ được hiển thị để thay thế cho nội dung bạn cần.

Ví dụ:

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click for report")
sẽ hiển thị: Click for report, và khi bạn click vào ô này, Excel sẽ mở bảng tính budget report.xls của trang web http://example.microsoft.com/report

=HYPERLINK("F:\GPE\book2.xls",F10)
sẽ hiển thị nội dung của ô F10 và khi bạn click vào ô này, Excel sẽ mở bảng tính book2.xls ở thư mục GPE của ổ F

=HYPERLINK("F:\GPE\book2.xls",F10)
sẽ hiển thị nội dung của ô F10 và khi bạn click vào ô này, Excel sẽ mở bảng tính book2.xls ở thư mục GPE của ổ F
​

...........................

Nếu bạn đã từng dùng cú pháp URL khi chèn liên kết trong diễn đàn:

HYPERLINK(link_location) tương tự như [noparse]link_location[/noparse]

HYPERLINK(link_location,friendly_name) tương tự như [noparse]friendly_name[/noparse]
​

 

[BNTT]

Hàm INDIRECT

Trả về một tham chiếu từ chuỗi ký tự. Tham chiếu được trả về ngay tức thời để hiển thị nội dung của chúng. Cũng có thể dùng hàm INDIRECT khi muốn thay đổi tham chiếu tới một ô bên trong một công thức mà không cần thay đổi công thức đó.

Cú pháp: =INDIRECT(ref_text [, a1])

ref_text là tham chiếu tới một ô (có thể là dạng A1 hoặc dạng R1C1), là tên định nghĩa của một tham chiếu, hoặc là một tham chiếu dạng chuỗi.

Nếu ref_text không hợp lệ, INDIRECT() sẽ báo lỗi #REF!
Nếu ref_text chứa tham chiếu đến một bảng tính khác thì bảng tính này phải đang mở, nếu không, INDIRECT() cũng báo lỗi #REF!
​

a1 là giá trị logic xác định dạng tham chiếu bên trong ref_text.

a1 = TRUE (hoặc là 1, hoặc không nhập) là kiểu tham chiếu A1
a1 = FALSE (hoặc là 2) là kiểu tham chiếu R1C1
​

Ví dụ:

​

Bạn để ý nhé.
Nếu B4 chỉ là số 10, thì INDIRECT() sẽ báo lỗi #REF!
Nhưng do tôi đã đặt tên cho B4 là George (tức là nội dung của A4) nên không bị lỗi.

​

 

[BNTT]

Hàm LOOKUP

Dùng để dò tìm một giá trị từ một dòng hoặc một cột trong một dãy ô hoặc một mảng giá trị.

Hàm LOOKUP() có hai dạng: Vec-tơ (vector form) và Mảng (array form)
​

• Dạng Vec-tơ: LOOKUP() tìm kiếm trên một dòng hoặc một cột, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) được chỉ định.
  
  
• Dạng Mảng: LOOKUP() tìm kiếm trên dòng (hoặc cột) đầu tiên của một mảng giá trị, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) cuối cùng trong mảng đó.

Vector form (dạng vec-tơ)

Cú pháp: LOOKUP(lookup_value, lookup_vector, result_vector)

lookup_value: Là giá trị LOOKUP() sẽ tìm kiếm trong lookup_vector. Nó có thể là một số, một ký tự, một giá trị logic, một tên đã được định nghĩa của một vùng ô hoặc một tham chiếu đến một giá trị.

lookup_vector: Là một vùng mà chỉ gồm một dòng (hoặc một cột) có chứa lookup_value. Những giá trị chứa trong vùng này có thể là một số, một ký tự hoặc một giá trị logic.

- lookup_vector phải được sắp xếp theo thứ tự tăng dần, nếu không, LOOKUP() có thể cho kết quả không chính xác.
- Nếu không tìm thấy lookup_value trong lookup_vector thì LOOKUP() sẽ lấy giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value.
- Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong lookup_vector thì LOOKUP() sẽ báo lỗi #NA!
​

result_vector: Là một vùng mà chỉ gồm một dòng (hoặc một cột) chứa giá trị trả về. Kích thước của result_vector bắt buộc phải bằng kích thước của lookup_vector.​

Ví dụ

​

Array form (dạng mảng)

Cú pháp: LOOKUP(lookup_value, array)

lookup_value: Là giá trị LOOKUP() sẽ tìm kiếm trong array. Nó có thể là một số, một ký tự, một giá trị logic, một tên đã được định nghĩa của một vùng ô hoặc một tham chiếu đến một giá trị.

- Nếu không tìm thấy lookup_value trong array thì LOOKUP() sẽ lấy giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value.
- Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột hoặc hàng đầu tiên trong array thì LOOKUP() sẽ báo lỗi #NA!
​

array: Là một vùng chứa lookup_value, có thế là số, ký tự, hoặc giá trị logic.

Dạng mảng của LOOKUP() gần tương đương như hàm VLOOKUP() hoặc HLOOKUP().
Khác biệt ở chỗ VLOOKUP() và HLOOKUP() tìm kiếm trên cột (hoặc dòng) đầu tiên, còn LOOKUP() tìm kiếm trên cột hoặc trên dòng tùy thuộc vào dạng mảng được khai báo:

- Nếu array là mảng có số cột nhiều hơn số dòng thì LOOKUP() sẽ tìm trên dòng đầu tiên.
- Nếu array là mảng có số dòng nhiều hơn số cột thì LOOKUP() sẽ tìm trên cột đầu tiên.
- Trường hợp array là mảng có số dòng bằng số cột thì LOOKUP() sẽ tìm trên cột đầu tiên.
- VLOOKUP() và HLOOKUP() lấy kết quả trên cột (hoặc) dòng được chỉ định, còn LOOKUP() luôn luôn lấy kết quả trên dòng (hoặc cột) cuối cùng.
- Các giá trị trên dòng (hoặc cột) đầu tiên của array phải được sắp xếp theo thứ tự tăng dần, nếu không, LOOKUP() có thể cho kết quả không chính xác.
​

Ví dụ

​

Xem thêm: Formulas & Functions Excel 2007 - 9. Working with Lookup Functions​

 

[BNTT]

Hàm OFFSET

Đây là một trong những hàm rất hay của Excel, và được ứng dụng rất nhiều.
Nó dùng để tham chiếu đến một vùng nào đó, bắt đầu từ một ô, hoặc một dãy ô, với một khoảng cách được chỉ định.

Cú pháp: =OFFSET(reference, rows, cols [, height] [, width])

reference: Là vùng mà bạn muốn làm điểm xuất phát để tham chiếu đến vùng khác. Reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không, hàm sẽ báo lỗi #VALUE!

rows: Số dòng dời lên (hoặc xuống) tính từ reference, nhập số dương nếu muốn dời xuống, hoặc số âm nếu muốn dời lên.

cols: Số cột dời sang phải trái (hoặc phải) tính từ reference, nhập số dương nếu muốn dời sang phải, hoặc số âm nếu muốn dời sang trái.

height: Là số dòng (độ cao) của vùng tham chiếu cần trả về.

width: Là số cột (độ rộng) của vùng tham chiếu cần trả về.
​

Ghi chú:

• Nếu số dòng (rows) hoặc cột (cols) vượt ra ngoài phạm vi bảng tính, hàm sẽ báo lỗi #REF!
• Độ cao (height) và độ rộng (width) nếu không nhập, thì xem như nó bằng với độ cao và độ rộng của vùng xuất phát (reference)

Ví dụ:

​

​

Tham khảo thêm: OFFSET TRUYỀN KỲ - Một bài rất hay của ttphong2007

 

[BNTT]

Hàm ROW

Trả về số thứ tự dòng của ô đầu tiên ở góc trên bên trái của vùng tham chiếu.

Cú pháp: =ROW(reference)

reference: Là ô hoặc một vùng nhiều ô. Nếu reference bỏ trống thì ROW() trả về số thứ tự dòng của ô chứa công thức.
​

Ví dụ:

ROW(A10) = 10
ROW(Y5:Z6) = 5
​

Hàm ROWS

Trả về số dòng của vùng tham chiếu.

Cú pháp: =ROWS(array)

array: Là ô hoặc một vùng nhiều ô, mảng tham chiếu
​

Ví dụ:

ROWS(A10:C18)= 8 (có 8 hàng trong vùng này)
​

__________________

 

[BNTT]

Hàm TRANSPOSE

Chuyển một vùng dữ liệu ngang thàng dọc và ngược lại.
Hàm TRANSPOSE() luôn luôn được nhập ở dạng công thức mảng (nhấn Ctrl-Shift-Enter sau khi nhập)


Cú pháp: =TRANSPOSE(array)

array: Là mảng dữ liệu cần hoán chuyển

Nếu số cột trong vùng nhập công thức nhiều hơn số hàng của array, hoặc số hàng trong vùng nhập công thức nhiều hơn số cột của array, hàm sẽ báo lỗi #NA tại những ô bị dư ra.​

Ví dụ:

​

---------- HẾT PHẦN EXCEL'S LOOKUP FUNCTIONS ----------
​