Cách kết hợp hàm Index Match trong Excel để dò tìm đơn giá

Có phải bạn không biết cách sử dụng hàm Index Match trong Excel và thường dò tìm dữ liệu bằng hàm Vlookup, Hlookup đúng không? Dùng hàm Vlookup, Hlookup cũng được thôi nhưng với những dữ liệu phức tạp, nhiều mảng, chúng ta không thể thực hiện được.

Hơn nữa, hàm Vlookup bị giới hạn rằng giá trị trả về phải nằm ở cột bên phải so với giá trị tìm kiếm. Còn giá trị trả về của hàm Hlookup phải nằm ở cột bên dưới. Vì vậy, hãy cùng Tin học Đại Dương tìm hiểu cách kết hợp hàm Index và hàm Match để tăng độ chính xác và hiệu suất cho công việc nhé!

Cú pháp hàm Index Match

Công thức hàm Index trong Excel

Hàm Index trong Excel trả về giá trị là tham chiếu chéo giữa Dòng và Cột.

Cú pháp hàm =INDEX(array; row_num; column_num)

                       = INDEX(bảng; dòng; cột)

Trong đó:

• array (Bảng): Vùng dữ liệu hay một hàng số mảng.
• row_num (Dòng): Thứ tự của hàng trong mảng, từ đó trả về một giá trị.
• column_num (Cột): Thứ tự của cột trong mảng, từ đó trả về một giá trị.

Công thức hàm Match trong Excel

Hàm Match là hàm trả về con số chỉ vị trí của giá trị dò trong mảng 1 chiều ngang hoặc dọc.

Cú pháp hàm = MATCH(lookup_value,lookup_array,match_type)

                       =MATCH(Giá trị dò, Bảng dò, Cách dò)

Trong đó:

• lookup_value (Giá trị dò): Giá trị cần tìm trong bảng dò.
• lookup_array (Bảng dò): Phạm vi ô hoặc mảng để tìm kiếm.
• match_type: Kiểu dò. Số 0 là dò chính xác. Số 1 là dò gần đúng.

Hướng dẫn sử dụng hàm Index và Match trong Excel

Hướng dẫn nhanh cách thực hiện

Trong ví dụ dưới đây, mình cần tìm ĐƠN GIÁ theo SẢN PHẨM và LOẠI trong Bảng Đơn Giá (H3:L8).

Để điền được cột đơn giá trong bảng Doanh Số Tháng 8, mình sử dụng công thức mảng, kết hợp hàm Index Match trong Excel để dò tìm.

Bước 1: Bôi đen toàn bộ cột Đơn giá (E4:E15).

Bước 2: Kết hợp hàm Index Match, nhập công thức tại ô trên cùng như sau:

=INDEX($I$4:$L$8;MATCH(B4:B15;$H$4:$H$8;0);MATCH(C4:C15;$I$3:$L$3;0)

Bước 3: Nhấn tổ hợp phím CTRL + SHIFT + ENTER là xong.

Giải thích chi tiết

Cách sử dụng hàm Index Match để dò tìm Đơn giá cho ô E4:

Bước 1: Vùng dữ liệu trong đề bài là I4:L8 (đối số Bảng trong hàm Index).

Bước 2: Sử dụng hàm Match để xác định vị trí Hàng của sản phẩm Nồi Cơm trong vùng dữ liệu B4:B15.

=MATCH(B4;$H$4:$H$8;0)

Kết quả trả về là số 1. Có nghĩa là Nồi cơm nằm ở hàng số 1 trong bảng Đơn Giá.

Bước 3: Sử dụng hàm Match để xác định vị trí Cột tương ứng của Loại C trong phạm vi I3:L3 của bảng Đơn Giá.

=MATCH(C4;$I$3:$L$3;0)

Kết quả trả về là số 3. Có nghĩa là Loại C tương ứng với cột số 3 trong bảng Đơn Giá.

Bước 4: Sử dụng kết hợp hàm Index Match để trả về giá trị theo Hàng và Cột ở bảng Đơn Giá.

=INDEX($I$4:$L$8; MATCH(B4;$H$4:$H$8;0); MATCH(C4;$I$3:$L$3;0))

Đơn giá tương ứng trả về là 175 (giá trị tham chiếu chéo giữa dòng 1 và cột 3).

Bước 5: Sao chép công thức để làm Đơn giá cho các sản phẩm công lại.

Đặt con chuột ở vị trí góc dưới bên tay phải để xuất hiện dấu cộng. Kéo công thức xuống cho các ô còn lại là xong.

Một số lỗi thường gặp ở ở hàm Index và Match

Sau đây là một số lỗi thường gặp khi bạn kết hợp sử dụng hàm Index Match:

Lỗi #VALUE

Nguyên nhân: Sử dụng công thức mảng nhưng bấm Enter.

Cách khắc phục: Sau khi hoàn thành công thức, bấm tổ hợp phím Ctrl + Shift + Enter.

Lỗi #N/A

Nguyên nhân: Không bấm Ctrl + Shift + Enter; hàm Match không tìm thấy giá trị trong phạm vi dò tìm. Không có giữa đồng nhất thứ tự sắp xếp giá trị trong phạm vi dò tìm hoặc các giá trị trong kiểu dò Match_type của hàm Match.

Cách khắc phục: Kiểm tra lại công thức và dấu đóng mở ngoặc của các hàm Index Match.

Hy vọng bài viết này giúp bạn nắm rõ cách sử dụng hàm Index Match trong Excel. Đồng thời, biết cách ứng dụng chúng để tăng cường hiệu quả công việc của mình nhé!

Đăng ký ngay khoá học chứng chỉ CNTT nâng cao của Tin Học Đại Dương để thành thạo Excel bạn nhé!

—————————————————

          Tin Học Đại Dương

Học thật – Thi thật – Giá trị thật 
 Fanpage: Tin Học Đại Dương
 Website: tinhocdaiduong.vn
 Hotline: 028 37222786
 Cơ sở 1: Số 25, Đường 23, Phường Linh Chiểu, Tp Thủ Đức, HCM (0932413041)
 Cơ sở 2: Tầng 4, Nhà Văn Hóa Sinh Viên, Khu Đại Học Quốc Gia, Tp Thủ Đức, HCM (0979076051)