Hướng dẫn cách dùng kết hợp hàm index và match trong excel

Bài viết Hướng dẫn Cách dùng kết hợp hàm index và match trong excel thuộc chủ đề về cẩm nang tìm việc đang được rất nhiều bạn lưu tâm đúng không nào !! Hôm nay, Hãy cùng So1vn.vn tìm hiểu Hướng dẫn Cách dùng kết hợp hàm index và match trong excel trong bài viết hôm nay nha !
Các bạn đang xem bài viết : “Hướng dẫn Cách dùng kết hợp hàm index và match trong excel”

Hàm index và hàm match kết hợp với nhau đem tới nhiều hữu ích trong quy trình học tập và làm việc. Đặc biệt, trong quy trình tìm kiếm tổng giá trị ở các hàng, cột để tìm đến kết quả tốt nhất giúp bạn đơn giản khả năng thay thế được hàm tìm kiếm hàm vlookup và hàm hlookup. Để nắm rõ hơn chúng ta hãy cùng tìm hiểu trong bài viết này nha!

Việc dò tìm dữ liệu trong excel là một trong số những công việc quen thuộc đặc biệt với các bạn học tin học hay lĩnh vực kế toán. Thông thường, sẽ áp dụng hàm vlookup để tìm dữ liệu theo cột, hàm hlookup dò tìm theo hàng để tìm kiếm dữ liệu mong muốn.

mặc khác với cách này sẽ chỉ áp dụng trong quy trình so sánh mã hàng và tổng giá trị tương ứng trong bảng dò tìm duy nhất. Bởi vậy, nếu như cần tìm tổng giá trị ở cả hàng và cột thì nên áp dụng hàm index. Đặc biệt, khi hàm index kết hợp với hàm match thì đạt kết quả cao hơn so với hàm vlookup và hàm hlookup.

Hàm index và match trong excel

Hàm index và match

Hàm index trong excel

Hàm index là hàm tìm kiếm tổng giá trị ở một ô nào đó hay giao cột dòng nhất định trả về mảng trong excel.

Hàm index dạng mảng cú pháp lệnh:

(Array,Row_num,[Column_num])

Trong đó:

• Array: Phạm vi ô hoặc hằng số mảng (bắt buộc)
• Row_num: Chọn hàng trong mảng để trả về tổng giá trị
• Column_num: Chọn cột trong mảng để trả về một tổng giá trị
• Điều kiện bắt buộc phải có một trong hai đối số là Row_num và Column_num.

Hàm Index dạng tham chiếu cú pháp lệnh:

INDEX(Reference,Row_num,[Column_num],[Area_num])

Trong đó:

• Reference: Vùng tham chiếu (bắt buộc)
• Row_num: Số hàng trả về một tham chiếu (bắt buộc)
• Column_num: Số cột trả về một tham chiếu (tùy chọn)
• Area_num: Số vùng ô chứa tổng giá trị trong reference. Trong trường hợp, Area_num được bỏ qua thì hàm index dùng vùng 1 (tùy chọn)

Hàm Match trong excel

Hàm match là một trong số những hàm cơ bản trong excel hay hiểu đơn giản hàm này sẽ trả kết quả vị trí tương đối của tổng giá trị cần tìm trong 1 vùng. mặc khác thì hàm này còn khả năng thực hiện nhiều hơn thế.

Cú pháp lệnh: MATCH(Lookup_value,Lookup_array,[Match_type])

Trong đó:

• Lookup_value: tổng giá trị tìm kiếm bao gồm: con số, văn bản, tổng giá trị logic, tham chiếu ô đến một vài, văn bản, tổng giá trị logic
• Lookup_array: Mảng tìm kiếm (bắt buộc)
• Match_type: Kiểu tìm kiếm (không bắt buộc)

Có 3 kiểu tìm kiếm cơ bản:

• 1: Less than (
• 0: Exact match (Chính xác tổng giá trị tìm kiếm)
• -1: Greater than (>tổng giá trị tìm kiếm)

dùng kết hợp index và match trong excel

Để nắm rõ hơn cách kết hợp giữa hàm index và hàm match trong excel thì bạn khả năng áp dụng qua Bài tập 1 như sau:

dùng kết hợp hàm index và hàm match trong excel

ở bảng dữ liệu, yêu cần tìm kiếm xem nước nào có thủ đô Seoul. Công thức áp dụng tìm ra kết quả là: =INDEX(B1:B10,MATCH(“Seoul”,C1:c10,0)

Trong đó:

• B1:B10 là cột chứa dữ liệu cần tra cứu, tìm kiếm
• MATCH(“Seoul”,C1:C10,0) là điều kiện cho biết kết quả nằm ở vị trí nào trong bảng dữ liệu. Kết quả Seoul hiển thị ở vị trí hàng thứ 6 trong bảng tính. (Công thức INDEX sẽ là : INDEX(B1:B10,6)
• Khi kết hợp 2 hàm trong bảng tính này, Seoul sẽ nằm cùng dòng với nước có thủ đô Seoul chính là Hàn Quốc.

Tổng quát lại:

Cú pháp lệnh: =INDEX(Cột cần tra cứu giá tị, (MATCH(tổng giá trị dùng tra cứu, cột chứa tổng giá trị này,0))

Bài tập áp dụng hàm index match

Trong bài tập 2, cho bảng dữ liệu về hàm index và hàm match, bắt buộc áp dụng 2 hàm điền vào cột đơn giá cho mỗi mặt hàng.

Bảng dữ liệu hàm index và hàm match

Nhập công thức lệnh ở ô E6 =INDEX($B$15:$F$19,MATCH(A6,$B$15:$B$19,0),MATCH(B6,$B$15:$F$15,0))

Kết quả hiển thị như sau:

Nhập công thức hàm index và hàm match

➤ XEM THÊM NGAY: Cách dùng các hàm tìm kiếm trong Excel [HƯỚNG DẪN]

Cách lọc thông tin bằng hàm Vlookup và hàm index/match

dùng hàm VLOOKUP

Khi cần tìm kiếm tổng giá trị nhiều điều kiện mọi người thường áp dụng ngay tới hàm vlookup để dò tìm. Với cú pháp lệnh chi tiết: =VLOOKUP(tổng giá trị dò tìm, Vùng dữ liệu [có: Cột dò tìm + Cột kết quả], Thứ tự cột trả về, Tìm chính xác/gần đúng)

Từ công thức trên, ta áp dụng vào Bài tập 3 như sau:

Ví dụ hàm vlookup tìm kiếm nhiều điều kiện

• “Huỳnh Văn Lê” chính là tổng giá trị tìm kiếm, cột vùng từ A1:A8 cột chứa sẽ luôn nằm bên trái ngoài cùng với dữ liệu (A1:C8).
• A1:C8 vùng dữ liệu các bạn quét cần phải cố định bằng cách ấn F4 trước khi thao tác tiếp theo.
• Cột chứa tổng giá trị trả về tính theo thứ tự từ trái sang phải, từ cột chứa tổng giá trị dò tìm.
• Nhập 0 tương ứng với FALSE sẽ trả về kết quả tìm kiếm chính xác.

ở đây ta sẽ có công thức lệnh: =VLOOKUP(E2, $A$1:$C$8, 3, 0)

• E2: tổng giá trị cần tìm trong vùng dữ liệu tính từ A1:C8, dấu $ có ý nghĩa định vùng dữ liệu để tìm kiếm
• Cột thứ 3 chính là cột chứa dữ liệu trả về tính từ vị trí tổng giá trị dò tìm về bên phải. Kết quả đưa ra là 0

dùng hàm Index và Match

Vậy với hàm index/match sẽ chỉ ra rằng rằng sự thay đổi ngay khác biệt:

Cú pháp lệnh chi tiết: =INDEX(Cột kết quả, MATCH(tổng giá trị dò tìm, Cột dò tìm chứa tổng giá trị cần tìm, Tìm chính xác/gần đúng)

ở đây bạn sẽ thấy hàm có cú pháp như sau hiện ra: =INDEX(Vùng kết quả, Dòng, Cột).

• $C$1:$C$8: Vùng kết quả khác hoàn toàn khi dùng hàm vlookup. ở đây bạn chỉ chọn mỗi vùng dữ liệu cột điểm không chọn cả cột chứa tổng giá trị dò tìm.
• Hàm match (tổng giá trị dò tìm, vùng tìm kiếm, chính xác/tương đối). ở E3 tổng giá trị dò tìm, vùng tìm kiếm chỉ đúng vùng chứa tổng giá trị dò tìm (A1:A8). Tương tự với hàm vlookup vùng tìm kiếm luôn cần cố định vùng ($A$1:$A$8) cho ra kết quả chính xác là 0 hoặc FALSE.
• Áp dụng công thức trên khả năng thấy, hàm match sẽ trả về tổng giá trị kết quả là 2 tương ứng dòng tìm kiếm từ trên xuống INDEX($C$1:$C$8, 2) => Kết quả là 7.

Ví dụ hàm index/match tìm kiếm nhiều điều kiện

➤ XEM THÊM NGAY: Hàm vlookup trong excel, cách dùng chi tiết và ví dụ chi tiết

Vì sao hàm index/match dùng tốt hơn hàm vlookup?

So sánh hàm vlookup và hàm index/match

Vlookup là công thức một chiều

Vlookup dùng tra cứu dữ liệu từ trái qua phải. Quay lại Bài tập 1 đã áp dụng ở mục trên, chúng ta không thể dùng hàm vlookup để tìm kiếm kết quả. Hàm áp dụng kết quả nhất chính là hàm index và hàm match mới tìm kiếm được kết quả này.

Bảng dữ liệu so sánh hàm vlookup và hàm index/match trong excel

Vlookup bị sai nếu thêm hoặc bớt cột trong bảng tính

Khi dùng hàm vlookup bạn cần chỉ ra cột muốn áp dụng lấy tổng giá trị về. Trong trường hợp thêm cột ở giữa cột đầu tiên sẽ khiến tổng giá trị bị thay đổi ngay vị trí, điều này sẽ khiến hàm vlookup trả kết quả không chính xác. Ngược lại, khi dùng hàm index và hàm match, kết quả hiển thị chính xác không xảy ra tình trạng thay đổi ngay dữ liệu.

Vlookup gặp nhiều điều kiện khi tra cứu 2 chiều

Khi chúng ta muốn tra cứu dân số của Seoul vào năm 2001, nếu áp dụng hàm vlookup sẽ rất phức tạp, nhưng dùng hàm index và hàm match sẽ nhanh chóng hơn. Công thức lệnh như sau:

=INDEX(A1:F10,MATCH(“Seoul”,C1:C10,0), MATCH(“Năm 2001”, A1:F1,0))

Trong đó:

• Hàm match đầu tiên cho biết Seoul của Hàn Quốc nằm ở ở vị trí nào trong bảng dữ liệu.
• Hàm match thứ hai cho biết dân số năm 2001 của Seoul nằm ở cột nào.
• Hàm index dùng 2 thông tin và tìm ra chính xác dữ liệu bạn cần.

Bài tập so sánh thực tế

Quay lại bảng dữ liệu ở Bài tập 3, hàm vlookup bắt buộc cột chứa tổng giá trị dò tìm nằm ngoài cùng phía bên trái của dữ liệu. Trong trường hợp, dữ liệu nằm phía bên phải thì phải kết hợp hàm choose để tìm kết quả tương ứng.

Ví dụ áp dụng hàm dò tìm tổng giá trị

Cú pháp lệnh hàm vlookup kết hợp hàm choose: =CHOOSE(1,2, Cột chứa tổng giá trị dò tìm, Cột Kết quả)

Cú pháp lệnh tổng quát: =VLOOKUP(tổng giá trị dò tìm, CHOOSE(1,2, Cột chứa tổng giá trị dò tìm, Cột kết quả), Cột trả về[2], Tìm chính xác [0])

Trong trường hợp, dấu phẩy phân cách là dấu chấm phẩy “;” thì công thức lệnh sẽ là: =VLOOKUP(tổng giá trị dò tìm; CHOOSE(1 2; Cột chứa tổng giá trị dò tìm; Cột kết quả); Cột trả về[2]; Tìm chính xác [0])

Với hàm index/match từ ví dụ trên có công thức lệnh là: =INDEX(Vùng kết quả, MATCH(tổng giá trị dò tìm, Vùng dò tìm, Tìm chính xác)

Hi vọng với những chia sẻ hữu ích trên đây các bạn sẽ nắm được rõ hơn về cách dùng hàm index và match trong excel. Đây là 2 hàm thường nhật và thông dụng trong quy trình làm việc và học tập không thể bỏ qua. Đừng quên cập nhập thêm những kỹ năng tin học văn phòng cơ bản ở trang web này nha. Chúc các bạn thành công!