Hàm index và match là hai hàm rất đặc biệt trong excel có thể thay thế hoàn toàn hàm vlookup. Để hiểu rõ hơn về công dụng và ứng dụng của hai hàm này, chúng ta cùng nhau luyện tập cách sử dụng chỉ mục và so khớp các hàm thông qua các bài tập có lời giải.
bài tập 1: sử dụng hàm đối sánh để tìm vị trí theo hàng, cột
cho bảng tính sau:
nếu bạn muốn biết thông tin của người được gọi là “le thi thuy” thì bạn phải làm như thế nào? chắc chắn điều đầu tiên cần xác định là vị trí của người đó trong bảng tính này.
Sử dụng hàm đối sánh, bạn sẽ biết được một người ở hàng nào trong bảng.
chúng tôi có công thức sau:
= match (“le thi thuy”, cột họ tên, số 0)
- tại ô d12, nhập tên Lê Thị Thủy
- tên cột là c3: c8
- chỉ ra rằng cột chứa giá trị mong muốn là cột e, dải ô e3: e8
- chỉ ra hàng có chứa giá trị cần tìm kiếm, được chỉ định bởi hàm đối sánh ở trên
- sử dụng hàm đối sánh để tìm vị trí của dòng email trong cột email = đối sánh (email, vùng chứa email, 0)
- Kết hợp với hàm index để chỉ ra vị trí của cột chứa kết quả cần tìm kiếm, có tên của cột (cột c), phạm vi c3: c8
- tham chiếu giá trị mã mặt hàng trên dòng 3 (dòng chứa thông tin mã mặt hàng làm cơ sở so sánh)
- hàm đối sánh trả về 2, tương ứng với cột thứ hai
- xem giá trị mã công ty trong cột a (cột chứa thông tin mã công ty để làm cơ sở so sánh)
- hàm đối sánh trả về 3, tương ứng với dòng thứ ba
- Thay vì tham chiếu đến một cột cố định trong đối số đầu tiên, chúng tôi tham chiếu đến toàn bộ không gian bảng a3: e7.
- trong bảng này, số hàng được xác định bởi hàm đối sánh trong ô i3, số cột được xác định bởi hàm đối sánh trong ô i2.
- kết quả của hàm chỉ mục là giao của số hàng và cột được chỉ định.
thì hàm đối sánh trả về số 3, tương ứng với hàng thứ ba trong bảng dữ liệu ở trên.
tham khảo: cách sử dụng hàm đối sánh trong excel
vậy việc tìm số 3 có nghĩa là gì? Nói cách khác, có tác dụng gì khi biết người đó ở vị trí hàng thứ ba trong bảng?
Để trả lời câu hỏi này, hãy xem yêu cầu bên dưới
bài tập 2: kết hợp hàm index với match để tìm email theo tên
Nhìn vào bảng trên, chúng ta thấy rằng ở vị trí dòng 3, trong cột email, email cần tìm kiếm sẽ xuất hiện. để làm rõ ràng: chỉ cần căn chỉnh cột email, ở vị trí hàng 3 trong bảng, chúng tôi có kết quả chúng tôi cần.
đây là cách kết hợp hàm chỉ mục với đối sánh:
trong công thức của ô d15 tìm email, chúng ta thấy:
= index (e3: e8, match (d12, c3: c8,0))
công thức này, hàm chỉ mục có nhiệm vụ:
phần giao nhau giữa các hàng và cột trong hàm chỉ mục tìm kiếm là kết quả của email cần tìm kiếm.
tham khảo: 6 tính năng hiệu quả nhất của hàm index trong excel
bài tập 3: tìm kiếm từ phải sang trái với hàm chỉ mục kết hợp với đối sánh
Thông thường chúng tôi tìm kiếm theo hướng từ trái sang phải. nhưng nếu bạn phải tìm kiếm từ phải sang trái (ngược lại với bình thường) thì sao?
ví dụ như sau: tìm tên dựa trên thông tin email?
cột email là cột thứ ba, nằm ở bên phải của cột tên (cột đầu tiên).
vì vậy, để tìm kiếm tên dựa trên email, chúng tôi tìm kiếm từ phải sang trái.
Quy trình như sau:
= index (c3: c8, match (d12, e3: e8,0))
rất đơn giản, phải không?
bài tập 4: xác định đơn giá của một mặt hàng trong nhiều điều kiện
ví dụ: chúng tôi có danh sách đơn giá sau:
Làm thế nào để xác định đơn giá của bất kỳ mã sản phẩm nào, của bất kỳ công ty nào?
điều này tương đương với việc bạn phải xem xét 2 điều kiện cùng lúc: mã sản phẩm và mã công ty
Để thực hiện yêu cầu này, chúng tôi sẽ sử dụng hàm chỉ mục kết hợp với hàm đối sánh như sau:
bước 1: tìm mã hàng trong cột nào của bảng
= match (h2, a3: e3,0)
bước 2: xác định mã công ty trên dòng nào của bảng
= match (h3, a3: a7,0)
thì giao điểm của dòng 3, cột 2 là ô b5 (vì bảng bắt đầu từ hàng 3 trở đi nên dòng 5 tương ứng với vị trí của hàng 3 trong bảng đơn giá).
bước 3: xác định đơn giá để tìm kiếm bằng hàm chỉ mục
Phần còn lại là sử dụng hàm chỉ mục để kết nối các hàng và cột được xác định ở trên.
trong hàm chỉ mục này, chúng ta có:
= index (a3: e7, i3, i2)
kết luận
sau đó, qua 4 bài tập ví dụ, chúng ta có thể hình dung cách sử dụng hàm index kết hợp với hàm match để thực hiện tìm kiếm, tham chiếu và các yêu cầu tìm kiếm trong excel. khi áp dụng tốt phương pháp này, nó hoàn toàn có thể thực hiện các yêu cầu tra cứu phức tạp mà hàm vlookup không thể thực hiện được.
Thay vì phải cân nhắc sử dụng hàm vlookup hoặc hlookup, tạo một công thức dài khi chúng ta phải kết hợp vlookup với hàm đối sánh, hlookup với hàm đối sánh, chúng ta chỉ cần sử dụng chỉ mục kết hợp với đối sánh.
Bạn cũng có thể đọc các bài viết thú vị khác:
tại sao sử dụng chỉ mục và đối sánh tốt hơn sử dụng vlookup trong excel?
phân biệt vlookup với hlookup và cách sử dụng nó
hàm vlookup trong excel và các ứng dụng nâng cao phổ biến