sự kết hợp của hàm index kết hợp với hàm match sẽ mang lại sự linh hoạt hơn trong việc tìm kiếm so với các hàm khác trong excel. dien may xanh sẽ hướng dẫn bạn cách sử dụng kết hợp hai hàm này trong bài viết tiếp theo.
1 Sự khác biệt giữa chỉ mục + hàm đối sánh và vlookup, hàm hlookup
Hàm vlookup, hlookup là 2 hàm dùng để tra cứu dữ liệu và được nhiều người ưa chuộng. tuy nhiên, hàm vlookup, hlookup có hạn chế là giá trị trả về phải ở cột bên phải so với giá trị tra cứu bằng hàm vlookup và ở cột dưới cùng. / strong với chức năng hlookup.
Trong ví dụ sau, chúng ta có thể sử dụng hàm vlookup và hlookup để tìm giá trị của cột tên thương hiệu và hàng tên công ty trong 2 bảng màu vàng của 2 bảng dữ liệu màu xanh lam.
nếu thứ tự của bảng màu xanh lam bị đảo ngược , bạn sẽ không thể sử dụng các hàm vlookup và hlookup nữa.
sau đó nếu bạn cần phát hiện giá trị theo hướng ngược lại, hàm chỉ mục kết hợp với hàm đối sánh sẽ giúp bạn giải quyết vấn đề này.
Ngoài ra, các lợi ích khác khi sử dụng hàm chỉ mục kết hợp với hàm đối sánh so với hàm vlookup và hlookup là:
- chèn hoặc xóa cột một cách an toàn: vì hàm đối sánh giúp xác định trực tiếp cột chứa giá trị mong muốn
- không giới hạn kích thước dữ liệu cần tìm kiếm: với vlookup và các hàm hlookup, giá trị cần tra cứu không được vượt quá 255 ký tự.
- tốc độ xử lý nhanh hơn: Sử dụng hàm chỉ mục kết hợp với hàm đối sánh sẽ tăng tốc độ xử lý lên 10% đến 15% trên các bảng tính lớn.
xem thêm: hàm sumif
2 hàm chỉ mục và hàm đối sánh
công thức hàm chỉ mục
Hàm chỉ mục trả về giá trị dựa trên vị trí của các hàng và cột trong bảng hoặc phạm vi.
= index (array; row_num; column_num)
ở đâu:
- mảng: phạm vi ô hoặc một hàng số mảng;
- row_num: chọn các hàng trong mảng để trả về giá trị từ đó;
- column: chọn các cột trong mảng từ trả về một giá trị.
công thức hàm đối sánh
hàm đối sánh trả về vị trí tương đối của một giá trị trong một phạm vi.
= match (lookup_value, lookup_array, match_type)
ở đâu:
- lookup_value: Giá trị tra cứu trong lookup_array.
- lookup_array: Mảng hoặc phạm vi ô được tìm kiếm.
- match_type: Loại tra cứu.
3 hàm chỉ mục mẫu được kết hợp với hàm đối sánh
giả sử chúng ta có đơn giá theo sản phẩm và nhà sản xuất trong bảng 2 (b15: e18). Theo bảng 2, chúng ta phải điền đơn giá vào bảng 1 (b3: d12).
Cách chúng tôi sử dụng hàm chỉ mục kết hợp với hàm đối sánh để tìm giá trị của ô d4 như sau:
1. sử dụng hàm đối sánh để định vị hàng tương ứng của sản phẩm cdrom trong phạm vi b15: b18 của bảng 2:
kết quả 4 tương ứng với hàng số 4 trong bảng 2.
2. sử dụng chức năng đối sánh để định vị cột nhà sản xuất samsung tương ứng trong phạm vi b15: e15:
kết quả 2 tương ứng với cột 2 của bảng 2.
3. sử dụng hàm index kết hợp với 2 hàm đối sánh ở trên để trả về giá trị dựa trên các hàng và cột trong bảng 2:
kết quả trả về giá trị tương ứng của hàng 4 (hàng chuột), cột 2 (cột samsung) trong bảng 2 là 5.
sau đó chúng tôi sao chép công thức sang các ô khác để hoàn thành.
4 một số lưu ý khi sử dụng
Như đã đề cập ở phần 1, vì hàm vlookup và hlookup có những hạn chế, nếu giá trị trả về nằm ở cột bên trái hoặc cột phía trên giá trị tra cứu thì 2 công thức sẽ không hoạt động. hàm chỉ mục kết hợp với hàm đối sánh sẽ linh hoạt hơn nhiều khi bạn không cần quan tâm đến vị trí của các cột trả về.
Trong ví dụ sau, chúng ta có bảng 1 liệt kê thủ đô của các quốc gia và chúng ta cần điền dữ liệu tương ứng vào bảng 2. Trong trường hợp này, chúng ta không thể sử dụng hàm vlookup, nhưng hàm index kết hợp với kết hợp chức năng sẽ giải quyết vấn đề. vấn đề đã được giải quyết.
5 lỗi thường gặp
Khi sử dụng hàm chỉ mục kết hợp với hàm đối sánh, bạn sẽ gặp lỗi #na và lỗi #value. Nguyên nhân của lỗi này thường là:
lỗi #na
- khi hàm đối sánh không tìm thấy giá trị trong phạm vi tìm kiếm, nó sẽ trả về giá trị # n / a.
- khi sử dụng một phạm vi trong chỉ mục, hàm này sẽ tìm kiếm một giá trị thay thế , bạn phải nhấn ctrl + shift + enter để quay lại công thức mảng.
- Khi bạn sử dụng khớp, phải có sự nhất quán giữa giá trị trong đối số match_type và giá trị của thứ tự sắp xếp trong phạm vi phát hiện , nếu không, bạn sẽ nhận được giá trị # n / a.
error #value
nếu bạn đang sử dụng chỉ mục dưới dạng công thức mảng có khớp để khớp với một giá trị, bạn sẽ cần chuyển đổi công thức của mình thành công thức mảng bằng cách nhấn ctrl + shift + enter, nếu không, bạn sẽ thấy #value. xuất hiện.
trên đây là bài viết chia sẻ đến các bạn cách sử dụng hàm index kết hợp với hàm match trong excel. Tôi hy vọng những thông tin này đã giúp bạn hiểu và sử dụng 2 chức năng này trong công việc của mình.