=INDEX(C2:C6,MATCH(F2,A2:A6,0)) tìm hàng có F2 trong A2:A6 và trả về giá trị ở cùng hàng đó của C2:C6. MATCH tìm vị trí, INDEX lấy giá trị ở vị trí đó. Cách này chạy trên mọi phiên bản Excel và dò được sang bên trái.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
Đổi F2 thành Milk và G2 trả về $1,10. Đổi C2:C6 thành B2:B6 và nó trả về nhóm hàng thay vì giá.
INDEX và MATCH phối hợp với nhau thế nào
Công thức gồm hai bước trong một ô. Ở đây hai bước được tách ra hai ô, để bạn thấy mỗi phần trả về gì.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
MATCH(G1,A2:A6,0) trả về 4, vì Bread là mục thứ tư của A2:A6. INDEX(C2:C6,4) trả về mục thứ tư của C2:C6, là $2,40. Đặt MATCH vào trong INDEX thay cho G2 là bạn có công thức một ô. Hai quy tắc giúp nó chạy đúng:
- Hai vùng phải bắt đầu ở cùng một hàng và cao bằng nhau.
MATCH(...,A2:A6,0)đếm từ hàng 2, nên INDEX phải đọcC2:C6, không phảiC1:C6(sẽ trả về hàng phía trên). - Kết thúc MATCH bằng 0. Không có nó, MATCH khớp gần đúng với giả định cột A đã sắp xếp, và trên một danh sách tên nó có thể trả về vị trí của hàng sai. Trang MATCH trình bày ba kiểu khớp của hàm này.
Nếu giá trị không có trong danh sách, MATCH trả về #N/A và cả công thức cũng vậy. =IFNA(INDEX(C2:C6,MATCH(F2,A2:A6,0)),"Not found") hiện văn bản thay cho lỗi.
Dò tìm sang bên trái
VLOOKUP chỉ trả về các cột nằm bên phải cột cần tìm. INDEX và MATCH không quan tâm thứ tự: tìm trong cột D, trả về cột A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
P-310 trả về Bread. Nhập P-205 vào F2 để được Carrot. Với VLOOKUP, bạn sẽ phải chuyển cột Code lên đầu bảng trước.
Tra cứu hai chiều: INDEX với hai MATCH
INDEX nhận một số thứ tự hàng và một số thứ tự cột. Hãy đưa cho nó cả bảng, để một MATCH tìm hàng và một MATCH khác tìm cột.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
East là hàng 3 của A2:A5 và Mar là cột 3 của B1:D1, nên INDEX trả về hàng 3, cột 3 của B2:D5: 5.600. MATCH cho hàng tìm dọc xuống cột đầu tiên, MATCH cho cột tìm ngang theo hàng tiêu đề, và cả hai vùng đều thẳng hàng với bảng B2:D5.
Vì sao INDEX MATCH tốt hơn VLOOKUP
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
Cả hai đều trả về giá. Sự khác biệt lộ ra khi trang tính thay đổi:
- Chèn thêm cột. Chèn một cột giữa Category và Price, VLOOKUP vẫn lấy cột 3, giờ là cột mới đang trống. Excel tự chỉnh
C2:C6trong phiên bản INDEX thànhD2:D6và công thức vẫn chạy đúng. - Dò tìm sang trái. Như ở trên: VLOOKUP không làm được, INDEX MATCH thì được.
Trong Excel 2021 và Microsoft 365, XLOOKUP làm được cả hai trong một hàm với các đối số đơn giản hơn. INDEX MATCH vẫn là lựa chọn cho các tệp phải mở được trong Excel 2019 hoặc cũ hơn, và riêng phần INDEX cũng rất hữu ích. Để tra cứu theo hai điều kiện cùng lúc, xem tra cứu nhiều điều kiện.
Luyện tập: dò tìm sang trái
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
Đến lượt bạn: Tên sản phẩm nằm ở cột cuối cùng. Trong G2, trả về giá của sản phẩm ở F2 bằng INDEX và MATCH.
Luyện tập: tra cứu hai chiều
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
Đến lượt bạn: Trong B9, trả về điểm của học sinh ở B7 cho môn học ở B8.
Câu hỏi thường gặp
INDEX MATCH hoạt động thế nào?
MATCH tìm vị trí của một giá trị trong một cột, còn INDEX trả về giá trị ở vị trí đó trong cột khác. Trong =INDEX(C2:C6,MATCH("Pear",A2:A6,0)), MATCH trả về 2 vì Pear là mục thứ hai của A2:A6, và INDEX trả về mục thứ hai của C2:C6.
Vì sao dùng INDEX MATCH thay cho VLOOKUP?
Nó trả về được cột nằm bên trái cột cần tìm, và không bị hỏng khi chèn thêm cột vào giữa bảng (vì không có số thứ tự cột nào bị lệch). Trong Excel 2021 và Microsoft 365, XLOOKUP cho cùng các ưu điểm đó chỉ với một hàm.
Số 0 trong MATCH có nghĩa là gì?
Nó yêu cầu khớp chính xác. Không có nó, MATCH dùng match_type 1, kiểu khớp gần đúng yêu cầu cột được sắp xếp tăng dần, và trên một danh sách chưa sắp xếp nó có thể trả về vị trí của hàng sai.
Làm sao tra cứu hai chiều bằng INDEX MATCH?
Đưa cho INDEX cả bảng và hai MATCH, một cho hàng và một cho cột: =INDEX(B2:D5,MATCH("South",A2:A5,0),MATCH("Feb",B1:D1,0)) trả về giá trị ở chỗ hàng South giao với cột Feb.
