[Kách Hay] Cách kết hợp hàm Index và Match trong Excel, có ví dụ cụ thể mới nhất 2023

Cách kết hợp hàm Index và Match trong Excel, có ví dụ cụ thể – Kách Hay .Com đã tổng hợp thông tin từ nhiều nguồn, giúp bạn có góc nhìn đa chiều hơn. Nào chúng ta bắt đầu thôi

New Page

Xem nhanh

Sử dụng kết hợp hàm chỉ mục và hàm khớp trong Excel với các ví dụ cụ thể

4/5 ( 65 bình chọn )

Lê Thị Thảo Tâm 28/10/2022, 15:07

Hàm index và hàm match kết hợp với nhau mang lại nhiều thuận lợi khi học tập và làm việc. Đặc biệt khi tìm kiếm giá trị theo hàng, cột để tìm được kết quả tốt nhất bạn có thể dễ dàng thay thế hàm tìm kiếm bằng hàm vlookup và hàm hlookup. Để hiểu rõ hơn, hãy cùng tìm hiểu trong bài viết này nhé!

Hàm Index và Match trong Excel

hàm index trong excel

Hàm chỉ mục là một hàm tìm kiếm một giá trị trong một ô cụ thể hoặc chỉ định một cột hàng cụ thể để trả về một mảng trong Excel.

Hàm chỉ mục có dạng một mảng cú pháp lệnh:

(mảng,số_hàng,[số_cột])

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ề giá trị
Column_num: Chọn cột trong mảng để trả về một 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.

Chức năng chỉ mục để tham khảo cú pháp lệnh :

INDEX(tham chiếu,số_hàng,[số_cột],[số_khu_vực])

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

Match-Funktion in Excel

Hàm match là một trong 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 giá trị cần tìm trong 1 vùng. Tuy nhiên thì hàm này còn có thể thực hiện nhiều hơn thế.

Cú pháp lệnh:

MATCH(Lookup_value,Lookup_array,[Match_type])

Trong đó:

Lookup_value: Giá trị tìm kiếm bao gồm: con số, văn bản, giá trị logic, tham chiếu ô đến một số, văn bản, 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 (<=”” li=””>
0: Exact match (Chính xác giá trị tìm kiếm)
-1: Greater than (>giá trị tìm kiếm)

Kết hợp giữa 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 có thể tham khảo các ví dụ sau:

Ví dụ 1: Tại bảng dữ liệu, yêu cần tìm kiếm xem nước nào có thủ đô Seoul.

Sử dụng kết hợp hàm index và hàm match trong excel

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ị tại 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(giá trị dùng tra cứu, cột chứa giá trị này,0))

Ví dụ 2: Cho bảng dữ liệu về hàm index và hàm match, yêu cầu á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 tại ô 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 sử 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 index/match

Cú pháp lệnh:

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

Ví dụ 3:

Tại đâ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).

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 sử dụng chi tiết và ví dụ cụ thể

$C$1:$C$8: Vùng kết quả khác hoàn toàn khi sử dụng hàm vlookup. Tại đâ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 giá trị dò tìm.
Hàm match (giá trị dò tìm, vùng tìm kiếm, chính xác/tương đối). Tại E3 giá trị dò tìm, vùng tìm kiếm chỉ đúng vùng chứa 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 có thể thấy, hàm match sẽ trả về 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ì sao hàm index/match sử dụng tốt hơn hàm vlookup?

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

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

Khi sử dụng hàm vlookup bạn cần chỉ ra cột muốn áp dụng lấy giá trị về. Trong trường hợp thêm cột ở giữa cột đầu tiên sẽ khiến giá trị bị thay đổi 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 sử 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 dữ liệu.

Vlookup gặp nhiều khó khă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.

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

Quay lại bảng dữ liệu ở ví dụ 3, hàm vlookup yêu cầu cột chứa 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 giá trị

Cú pháp lệnh hàm Vlookup kết hợp hàm Choose:

=CHOOSE({1,2}, Cột chứa giá trị dò tìm, Cột Kết quả)

Cú pháp lệnh tổng quát:

=VLOOKUP(Giá trị dò tìm, CHOOSE({1,2}, Cột chứa 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(Giá trị dò tìm; CHOOSE({1 \ 2}; Cột chứa 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(Giá trị dò tìm, Vùng dò tìm, Tìm chính xác)

Những lưu ý khi sử dụng hàm INDEX/MATCH

Khi MATCH trả về lỗi #N/A sẽ bắt nguồn vì nhiều lý do:

match_type là 0, lookup_value không được tìm thấy

match_type là -1, lookup_array không theo thứ tự giảm dần

match_type là 1, lookup_array không theo thứ tự tăng dần và lookup_array không phải là một hàng hoặc một cột.

INDEX sẽ trả về lỗi #REF! khi column_num, row_num không trỏ đến một ô trong mảng
MATCH không phân biệt chữ thường với chữ hoa mà được xử lý giống nhau

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 sử dụng hàm index và match trong excel. Đây là 2 hàm phổ biến và thông dụng trong quá 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 tại trang web này nhé. Chúc các bạn thành công!

Lê Thị Thảo Tâm

Với gần 6 năm kinh nghiệm làm việc trong lĩnh vực tuyển dụng việc làm, tư vấn nghề nghiệp. Mình hi vọng những kiến thức chia sẻ trên website sẽ giúp ích được cho bạn trong việc tìm kiếm công việc phù hợp, thu nhập hấp dẫn

Công nghệ Ứng dụng Tin học Văn phòng Microsoft Excel

Cách kết hợp hàm Index và Match trong Excel, có ví dụ cụ thể

Dương Nguyễn

Hàm tìm kiếm trong Excel thuộc nhóm hàm cơ bản Excel và có rất nhiều lựa chọn khác nhau để bạn tìm được giá trị mình cần trong bảng, như dùng hàm Vlookup Excel hay hàm Hlookup. Tuy nhiên 2 hàm tìm kiếm trên sẽ chỉ tìm giá trị theo hàng hoặc cột, theo 1 chiều mà không tìm kiếm giá trị trên cả hàng và cột.

Với trường hợp này chúng ta nên kết hợp giữa hàm Index và Match để lấy các giá trị ở các hàng và các cột, dễ dàng thực hiện hơn để thay thế cho hàm tìm kiếm Vlookup hay Hlookup. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách kết hợp 2 hàm Index và Match trong Excel.

Dùng hàm VLOOKUP để nối gộp hai bảng Excel với nhau
Cách kết hợp hàm Sumif và hàm Vlookup trên Excel
Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung
2 cách để tách thông tin Họ và Tên trong Microsoft Excel?

Hướng dẫn kết hợp hàm Index và Match trong Excel

1. Hàm Index trong Excel

Hàm Index có 2 dạng là hàm Index dạng mảng và Index dạng tham chiếu.

Hàm Index dạng mảng trả về giá trị của 1 ô dữ liệu có chỉ số hàng và chỉ số cột đang tìm kiếm. Cú pháp hàm dạng mạng là Index (Array, Row_num,[column_num]). Trong đó:

Array: Mảng dữ liệu tham chiếu.
Row_num: Hàng chứa giá trị cần lấy.
Column_num: Cột chứa giá trị cần lấy.

Hàm Index dạng tham chiếu trả về giá trị của 1 ô có chỉ số hàng và cột đang tìm kiếm. Cú pháp hàm dạng tham chiếu là INDEX (Reference, Row_num, [Column_num], [Area_num]. Trong đó:

Reference: Vùng tham chiếu chứa giá trị cần tìm.
Row_num: Chỉ số hàng chứa giá trị cần tìm.
Column_num: Chỉ số cột chứa giá trị cần tìm.
Area_num: Số vùng trả về, để trống mặc định là 1.

Tìm hiểu thêm về cách sử dụng hàm Index trong bài viết Cách dùng hàm Index trong Excel.

2. Hàm Match trong Excel

Hàm Match trả về số thứ tự của giá trị cần tìm trong bảng. Cấu trúc hàm Match là =MATCH (Lookup_Value, Lookup_array, [Match_type]). Trong đó:

Lookup_Value: Giá trị cần tìm kiếm.
Lookup_array: Mảng chứa giá trị cần tìm.
Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:
- Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.
- Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.
- Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.

Cách dùng hàm Match bạn đọc trong bài viết Cách dùng hàm Match trong Excel.

3. Kết hợp hàm Match với hàm Index

Chúng ta có bảng dữ liệu như hình dưới đây.

Bài tập 1: Tìm kiếm chất liệu của mặt hàng Tất cổ chân trong bảng (Tìm từ phải qua trái)

Theo bảng chúng ta thấy vị trí của Tất cổ chân nằm ở STT 4 và dóng sang sẽ thấy chất liệu của Tất cổ chân là Len dệt kim. Như vậy chúng ta sẽ nhìn vào cột Chất liệu và tìm tới dòng số 5 sẽ ra được kết quả.

Bước 1:

Trước hết chúng ta sẽ tìm mặt hàng Tất cổ chân nằm ở vị trí nào trong bảng. Nhập công thức =MATCH(“Tất cổ chân”,B1:B7,0) rồi nhấn Enter. Trong đó:

Tất cổ chân: Là giá trị cần tìm đúng vị trí.
B1:B7: Vùng tìm kiếm cho giá trị, ở đây là cột Mặt hàng.
0: Tìm giá trị chính xác.

Kết quả sẽ ra 5, nghĩa là Tất cổ chân ở vị trí dòng thứ 5 trong bảng.

Bước 2:

Bây giờ chúng ta sẽ tìm giá trị tại cột Chất liệu ứng với giá trị tại dòng số 5, sẽ ra được chất liệu cho mặt hàng Tất cổ chân.

Công thức kết hợp hàm Index với hàm Match là =INDEX(cột cần tra cứu giá trị, (MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)).

Áp dụng vào bài tập 1 thì có thể thay cả cụm MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)) = 5, thứ tự của giá trị dùng để tra cứu.

Nhập công thức là =INDEX(D1:D7,5) và nhấn Enter. Trong đó D1:D7 là cột chứa giá trị cần tra cứu.

Kết quả sẽ ra chất liệu Len dệt Kim.

Công thức tổng quát kết hợp hàm Index với hàm Match khi áp dụng vào bài tập 1 là =INDEX(D1:D7,MATCH(“Tất cổ chân”,B1:B7,0)) rồi nhấn Enter.

Kết quả cũng cho ra Len dệt kim ứng với Tất cổ chân.

Übung 2: Artikel finden, die dem Leinenmaterial in der Tabelle entsprechen (Suche von links nach rechts)

Schritt 1:

Zuerst müssen wir die Position von Leinen in der Spalte Material finden . Sie geben die Formel =MATCH(“Linen”,D1:D7,0) ein und drücken dann die Eingabetaste. Innerhalb:

Leinen: Es muss ein Standort für diesen Wert gefunden werden.
D1:D7: Der Datenbereich enthält den Wert, um den Ort zu finden.
0: Finde genau diesen Wert.

Ausgabe 7, Leinen steht in Zeile 7 in der Spalte Material.

Schritt 2:

Kombiniert mit der Index-Funktion haben wir die Formel =INDEX(B1:B7,7) und drücken dann die Eingabetaste.

Die Ergebnisse zeigen Damen-Shorts, die der Tabelle entsprechen.

Wir kombinieren die Index-Funktion mit der Match-Funktion in derselben Eingabeformel =INDEX(B1:B7,MATCH(“Linen”,D1:D7,0)) und drücken dann die Eingabetaste.

Die Ergebnisse werden auch für Damen-Shorts angezeigt.

Wenn wir also die Index-Funktion mit der Excel-Funktion kombinieren, suchen wir nach Werten in beiden Richtungen von links nach rechts oder von rechts nach links. Mit der SVERWEIS-Funktion können wir Daten nur von links nach rechts nachschlagen.

Leser können die Übung, die die Index-Funktion mit der Match-Funktion kombiniert, mit der Lösung gemäß dem unten stehenden Link herunterladen.

Laden Sie die Übungs-INDEX-Funktion kombiniert mit der MATCH-Funktion herunter

Ich wünsche Ihnen viel Erfolg!

Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel
Hàm LEFT, cách dùng hàm cắt chuỗi ký tự bên trái trong Excel
Hàm DAYS trong Excel: Cách tính khoảng cách ngày trong Excel
Cách tìm ô trống Excel, tô màu ô trống trong Excel

Thứ Năm, 14/10/2021 10:17

4,4 ★ 7 👨 48.940

0 Bình luận

Sắp xếp theo Mặc định Mới nhất Cũ nhất

😀 😃 😄 😁 😆 😅 😂 🤣 😊 😇 🙂 🙃 😉 😌 😍 🥰 😘 😗 😙 😚 😋 😛 😝 😜 🤪 🤨 🧐 🤓 😎 🤩 🥳 😏 😒 😞 😔 😟 😕 🙁 😣 😖 😫 😩 🥺 😢 😭 😤 😠 😡 🤬 🤯 😳 🥵 🥶 😱 😨 😰 😥 😓 🤗 🤔 🤭 🥱 🤫 🤥 😶 😐 😑 😬 🙄 😯 😦 😧 😮 😲 😴 🤤 😪 😵 🤐 🥴 🤢 🤮 🤧 😷 🤒 🤕 🤑 🤠 😈 👿 👹 👺 🤡 💩 👻 💀 👽 👾 🤖 🎃 😺 😸 😹 😻 😼 😽 🙀 😿 😾 🤲 👐 🙌 👏 🤝 👍 👎 👊 ✊ 🤛 🤜 🤞 ✌ 🤟 🤘 👌 🤏 👈 👉 👆 👇 ☝ ✋ 🤚 🖐 🖖 👋 🤙 💪 🦾 🖕 ✍ 🙏 🦶 🦵 🦿 💄 💋 👄 🦷 🦴 👅 👂 🦻 👃 👣 👀 🧠 🗣 👤 👥 👶 👧 🧒 👦 👩 🧑 👨 👱 🧔 👵 🧓 👴 👲 👳 🧕 👮 👷 💂 🕵 👰 🤵 👸 🤴 🦸 🦹 🤶 🎅 🧙 🧝 🧛 🧟 🧞 🧜 🧚 👼 🤰 🤱 🙇 💁 🙅 🙆 🙋 🧏 🤦 🤷 🙎 🙍 💇 💆 🧖 💅 🤳 💃 🕺 👯 🕴 🚶 🏃 🧍 🧎 👫 👭 👬 💑 💏 👪

Xóa Đăng nhập để Gửi

Bạn nên đọc

Cách phát WiFi từ điện thoại sang máy tính nhanh nhất
Fujifilm X10 giá 600 USD
Đặt mấy củ tỏi lên bàn thờ Thần Tài để hút tài lộc?
Cách đổi mật khẩu Zoom trên điện thoại, máy tính
Qualcomm trình làng Snapdragon 690, mang 5G đến với smartphone tầm trung
Rối loạn tiền đình là gì? Triệu chứng bệnh rối loạn tiền đình

Microsoft Excel

Cách dùng Wrap Text trong Microsoft Excel
Cách chuyển Excel thành XML
Cách lấy dữ liệu từ web vào Excel
Cách tách nền khỏi ảnh trong Excel
Cách sửa lỗi công thức Excel không nhảy
Cách tạo biểu đồ thanh trong Excel
Cách sửa lỗi mất khung in trong Excel
Cách lưu biểu đồ Excel dưới dạng ảnh
Công thức tính tiền điện, tải file Excel để tự tính tiền điện chính xác, nhanh chóng