Bài viết: Một số ví dụ đơn giản về hàm dò tìm và tham chiếu

[BNTT]

Để tìm hiểu kỹ hơn về các hàm dò tìm và tham chiếu, mời bạn vào đây.

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)
​

​

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))
​

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))}
​

Một số bài viết có liên quan:
1/ Offset truyền kỳ (kỳ 2)
2/ Offset truyền kỳ (kỳ 1)
3/ Kết hợp các chữ ở nhiều ô thành 1 dòng chỉ trong tíc tắc
4/ SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
5/ Sử dụng Excel để ra quyết định hằng ngày
6/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
7/ 10 lý do để học công thức Excel
8/ Làm việc với công thức mảng trong Excel
9/ VLOOKUP với Cột động
10/ Sử dụng hàm Subtotal

 

[tommytrant20]

thớt mình mà tô màu rùi dò tìm tham chiếu dc ko
ví dụ mình có 1 cột dữ liệu tô 2 mau vàng / xanh đếm loc xem có bao nhều cột vàng và bao nhiều cột xanh
tổng cột trắng ( ko xanh ko vàng ) thớt cho minh xin ý tưởng đoạn lệnh trên nha. tks thớt nhiều

 

[btnam82]

không có file ví dụ kèm theo hả chủ thớt ^^

 

[thanh318]

Bạn ơi cho mình hỏi ở ví dụ Tìm kiếm trong nhiều cột, mình dùng công thức MATCH(B1 & B2, A6:A14 & B6:B14, 0), tuy nhiên lại trả về giá trị là #Value là sao nhỉ?
Mình đã kiểm tra công thức bằng cách ấn chữ Fx ở cạnh ô nhập liệu, thấy vẫn cho ra giá trị là số thứ tự Row, tuy nhiên khi enter công thức thì giá trị lại là #Value?

 

[thanh318]

Mình làm được rồi, ấn enter thì k đc nma ấn Ctrl Shilf Enter thì lại đc??? K hiểu vì sao? Hic