INDEX và MATCH kết hợp với nhau sẽ tốt hơn sử dụng VLOOKUP trong nhiều trường hợp! Trong bài viết này, các ví dụ về sử dụng kết hợp INDEX và MATCH sẽ giúp các bạn xử lý những trường hợp mà VLOOKUP không xử lý được.
“Tại sao lại học thêm 2 hàm nữa?”. Bởi vì VLOOKUP không phải là công cụ duy nhất của bạn, INDEX và MATCH sẽ giúp bạn làm việc hiệu quả hơn, nhanh hơn, ít lỗi hơn. Ngoài ra, INDEX MATCH rất linh hoạt và có thể tuỳ biến cho nhiều trường hợp khác.
CÁCH SỬ DỤNG HÀM INDEX VÀ MATCH TRONG EXCEL CƠ BẢN
Vì bài viết này tập trung vào những trường hợp dùng INDEX và MATCH sẽ thuận lợi hơn việc dùngVLOOKUP nên để tìm hiểu cách sử dụng cơ bản của INDEX và MATCH mời các bạn xem video sau đây. File Excel đi kèm video các bạn download ở cuối bài.
HÀM INDEX
Hàm INDEX sẽ trả về dữ liệu của một ô trong 1 vùng dựa trên chỉ số hàng và chỉ số cột của vùng đó. Về cơ bản, hàm INDEX có cú pháp như sau:
=INDEX(vùng_dữ_liệu, hàng_thứ_mấy, [cột_thứ_mấy])vùng_dữ_liệu– là địa chỉ vùng dữ liệu chúng ta muốn “nhặt” ra 1 giá trịhàng_thứ_mấy– ô cần lấy dữ liệu nằm ở hàng thứ mấy?cột_thứ_mấy– ô cần lấy dữ liệu nằm ở cột thứ mấy?
Để thử dùng hàm INDEX chúng ta có thể thử trực tiếp câu lệnh sau trên bảng tính phía dưới
=INDEX(A1:D10,6,3)
Hàm INDEX tìm dữ liệu trong vùng
A1:D10 và trả lại dữ liệu trong dòng thứ 6, cột thứ 3, tức là ô C6. Kết quả của câu lệnh INDEX trên sẽ làSeoul như trong ô B14HÀM MATCH
Hàm MATCH tìm kiếm 1 giá trị trong 1 vùng của bảng tính và đưa lại vị trí tương đối của ô chứa giá trị đó trong vùng tìm kiếm. Cú pháp của hàmMATCH như sau:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])giá_trị_cần_tìm– giá trị cần được tìm kiếm vị trí trong mảngmảng_tìm_kiếm– mảng chứa giá trị cần tìm kiếmkiểu_tìm_kiếm– tìm kiếm giá trị chính xác hay tìm kiếm giá trị gần nhất.1– hoặc bỏ qua: tìm giá trị lớn nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. Giá trị trong mảng_tìm_kiếm cần được sắp xếp tăng dần0– tìm giá trị đầu tiên trong mảng bằng với giá_trị_cần_tìm. Đây là giá trị mà chúng ta sẽ rất hay dùng khi sử dụng kết hợpINDEX/MATCH-1– tìm giá trị nhỏ nhất mà giá trị đó lơn hơn hoặc bằng giá_trị_cần_tìm
Khi đọc đến đây, chắc các bạn sẽ tự hỏi là Cái hàm MATCH này hình như không tác dụng lắm thì phải. Đúng thế, nếu chỉ như vậy thì hàmMATCH không có nhiều tác dụng lắm. Nhưng ở phần tiếp theo, khi kết hợp cùng hàm INDEX thì chúng ta hãy xem xem cặp đôi này làm gì được cho các bạn.
SỬ DỤNG KẾT HỢP INDEX VÀ MATCH TRONG EXCEL
Chúng ta sẽ đi làm ngay một ví dụ trước để các bạn có thể hiểu được sự kết hợp của hàm INDEX và MATCH trong Excel như thế nào. Chúng ta có bảng Excel sau đây:
Chúng ta muốn đi tìm kiếm xem nước nào có thủ đô là Seoul trong bảng này. Công thức để tìm ra điều đó như sau:
=INDEX(B1:B10,MATCH("Seoul",C1:C10,0)
Trong công thức này:
B1:B10là cột chứa dữ liệu mà chúng ta muốn tra cứu, tìm kiếm hoặc trích lọcMATCH("Seoul",C1:C10,0)sẽ cho chúng ta biết Seoul ở vị trí hàng thứ mấy ở trong bảng tính trên. Kết quả Seoul ở vị trí hàng thứ 6 trong bảng tính. Công thức với INDEX trở thành:INDEX(B1:B10,6)- Khi kết hợp 2 công thức này lại và do tính chất của bảng tính, Seoul sẽ phải nằm cùng dòng với nước có thủ đô là Seoul nên ta có thể sử dụng cách này để tìm ra được kết quả là Hàn Quốc
Tổng quát lại thì chúng ta có công thức sau:
=INDEX( cột cần tra cứu giá trị, (MATCH ( giá trị dùng để tra cứu, cột chứa giá trị này, 0 ))
Và một lần nữa, các bạn có thể thực hành INDEX và MATCH trong bảng tính Excel online phía trên. Khi các bạn đã chắc tay với INDEX và MATCH* chúng ta sẽ xem phần tiếp theo tại sao **INDEX và MATCHlại tốt hơn VLOOKUP ở nhiều trường hợp.
VÌ SAO INDEX VÀ MATCH TỐT HƠN VLOOKUP
VLOOKUP LÀ CÔNG THỨC 1 CHIỀU
Với VLOOKUP, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải. Trong ví dụ tìm nước có thủ đô là Seoul ở trên, chúng ta sẽ không dùngVLOOKUP để có thể tìm ra Hàn Quốc được. Như các bạn đã thấy,INDEX và MATCH làm được điều này.
VLOOKUP SẼ BỊ SAI NẾU CHÚNG TA THÊM HOẶC BỚT CỘT Ở TRONG BẢNG TÍNH.
Cái này thì rõ ràng rồi, bởi vì khi dùng VLOOKUP các bạn phải chỉ ra cột nào chúng ta muốn lấy giá trị về. Khi thêm hoặc bớt 1 cột ở giữa cột đầu tiên và cột cần lấy giá trị về thì cột cần lấy giá trị bị xê dịch đi, dẫn đến kết quả của hàm VLOOKUP không đúng nữa. Với INDEX và MATCH thì điều này không xảy ra vì khi thêm/bớt cột thì công thức trong hàm INDEX và MATCH sẽ được điều chỉnh theo.
VLOOKUP SẼ GẶP KHÓ KHĂN KHI TRA CỨU 2 CHIỀU NHƯ TRONG VD SAU ĐÂY
Các bạn có thể mở
Sheet2 trong bảng tính phía trên ra. Nếu chúng ta muốn tra cứu dân số của Seoul vào năm 2001 thì trong trường hợp này chúng ta có thể dùng VLOOKUP – một cách khó khăn, nhưng dùng INDEX và MATCH sẽ nhanh gọn hơn như sau:=INDEX(A1:F10,MATCH("Seoul",C1:C10,0),MATCH("Năm 2001",A1:F1,0))- Hàm Match đầu tiên sẽ cho chúng ta biết thủ đô Seoul của Hàn Quốc nằm ở dòng thứ mấy
- Hàm Match thứ hai sẽ cho chúng ta biết dữ liệu dân số của Năm 2001 nằm ở cột nào
- Hàm Index sẽ sử dụng 2 thông tin trên và tìm ra dữ liệu chính xác cho chúng ta.
ỨNG DỤNG INDEX VÀ MATCH NÂNG CAO
DÒ TÌM DỮ LIỆU DỰA TRÊN NHIỀU CỘT ĐIỀU KIỆN
Các bạn hãy mở
Sheet3 trong bảng tính sau đây:
Yêu cầu bài này là chúng ta phải điền vào
D5:D16 dựa trên dữ liệu ở bảng tra cứu. Công thức như sau:{=INDEX($F$5:$H$16,MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0),3)}- Dấu
{}– thể hiện rằng đây là một công thức mảng, các bạn khi nhập công thức mảng cần sử dụng phím đặc biệt của Excel làCTRL + SHIFT + ENTER. Khi nhập công thức vào Excel, các bạn nhập bình thường, không cần dấu{}và sử dụng phímCTRL + SHIFT + ENTER. - Công thức trên chưa được hỗ trợ trực tiếp trong phiên bản hiện tại của Excel Online, nếu bạn muốn thử, có thể copy bảng tính này về Excel của bạn và thử công thức đó.
- Ảnh này để các bạn có thể hiểu rõ hơn cách hoạt động của công thức trên:
- Trong công thức trên, phần khó nhất là phần có hàm Match –
MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0):(A5=$F$5:$F$16): Chúng ta đi so sánh giá trị củaA5với vùng$F$5:$F$16. Kết quả nhận được là{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}(B5=$G$5:$G$16): Chúng ta đi so sánh giá trị củaB5với vùng$G$5:$G$16. Kết quả nhận được là{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}- (A5=$F$5:$F$16)*(B5=$G$5:$G$16) : Kết quả của phép tính này là
{0;1;0;0;0;0;0;0;0;0;0;0} - Do đó Hàm Match của chúng ta từ
MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0)trở thành:MATCH(1,{0;1;0;0;0;0;0;0;0;0;0;0},0)kết quả của hàm match này là 2, dòng thứ 2 trong bảng tra cứu sẽ thoả mãn điều kiện Dan Brown và Táo, Tổng phải tìm là 271
CÒN GÌ NỮA SAU BÀI VIẾT NÀY
Qua bài viết này, chắc các bạn cũng đã biết là ngoài VLOOKUP ra thì còn một công thức khó dùng khác nữa là INDEX và MATCH cũng khá là có ích khi mà gặp phải trường hợp VLOOKUP không xử lý được. Mong đây là bước khởi đầu tốt cho các bạn. Nếu bạn đã đọc đến đây, cảm ơn bạn rất nhiều.
