Hãy tưởng tượng bạn đang quản lý một bảng doanh số bán hàng với hàng trăm nhân viên và bạn cần tìm doanh số cụ thể của nhân viên? Hoặc bạn đang quản lý kho hàng và cần biết số lượng sản phẩm còn lại trong kho? Với hàm MATCH và INDEX, bạn có thể dễ dàng tìm ra câu trả lời cho những câu hỏi này chỉ trong vài giây. Trong bài viết này, ACE Academy sẽ giới thiệu về cách sử dụng bộ đôi hàm này, cùng với ví dụ cụ thể để minh họa.
1. Hàm MATCH trong Excel
1.1 Hàm MATCH trong Excel là gì?
Hàm MATCH trong Excel trả về vị trí của một giá trị trong một phạm vi hoặc mảng dữ liệu. Điều này hữu ích khi bạn tìm vị trí của một giá trị cụ thể trong một cột hoặc hàng. Nó thường được sử dụng kết hợp với các hàm khác như INDEX để tìm kiếm dữ liệu một cách linh hoạt và mạnh mẽ.
1.2 Cú pháp và cách sử dụng hàm MATCH
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: Giá trị bạn muốn tìm kiếm.
lookup_array: Phạm vi mà bạn muốn tìm kiếm giá trị.
match_type: Kiểu khớp, có thể là 1, 0, hoặc -1 (tùy chọn).
- 1 (mặc định): Tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value.
- 0: Tìm chính xác giá trị.
- -1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value.
1.2.1 Ví dụ với phạm vi mảng dọc
Nếu bạn muốn tìm vị trí của từ “peach” trong danh sách các loại trái cây. Bạn có thể sử dụng hàm MATCH như sau:
Công thức này sẽ trả về 3, vì “peach” là mục thứ 3 trong phạm vi B3. Lưu ý rằng hàm MATCH không phân biệt chữ hoa chữ thường
1.2.2 Ví dụ với phạm vi mảng ngang
Nếu bạn có một danh sách các loại trái cây nằm ngang và muốn tìm vị trí của “peach”. Bạn có thể sử dụng công thức:
Kết quả vẫn sẽ là 3, vì “peach” là mục thứ 3 trong phạm vi C4:i4
Lưu ý về tham số match_type:
Tham số cuối cùng trong hàm MATCH là match_type, rất quan trọng để xác định loại khớp mà bạn muốn:
- 0: Tìm kiếm khớp chính xác.
- 1(mặc định): Tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value.
- -1: Tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value.
Trong hầu hết các trường hợp, bạn nên sử dụng 0 để đảm bảo tìm kiếm khớp chính xác.
2.Hàm INDEX trong Excel
2.1 Hàm INDEX trong Excel là gì?
Hàm INDEX trong Excel là một hàm mạnh mẽ được sử dụng để truy xuất giá trị từ một mảng hoặc phạm vi dữ liệu dựa trên vị trí hàng và cột cụ thể. Nó cho phép bạn lấy dữ liệu từ bảng hoặc dải ô mà không cần phải dò thủ công. Đặc biệt hữu ích khi kết hợp với các hàm khác như MATCH.
2.2 Cú pháp và cách sử dụng hàm INDEX
array: Phạm vi hoặc mảng từ đó bạn muốn trả về giá trị.
row_num: Số hàng từ đó giá trị sẽ được trả về.
column_num: Số cột từ đó giá trị sẽ được trả về (tùy chọn).
Ví dụ:
Nếu bạn có một bảng danh sách các hành tinh trong hệ mặt trời. Và muốn tìm tên của hành tinh thứ 4, bạn có thể sử dụng hàm INDEX như sau:
INDEX trả về giá trị ở hàng thứ 4 của phạm vi.
Nếu bạn muốn lấy đường kính của Sao Hỏa (Mars) bằng hàm INDEX. Bạn có thể sử dụng hàm với cả số hàng và số cột để chỉ định vị trí chính xác hơn trong phạm vi dữ liệu lớn hơn.
Ví dụ: Để lấy đường kính của Sao Hỏa, bạn cần cung cấp phạm vi B3:D11 và chỉ định số hàng là 4 và số cột là 2. Công thức sẽ như sau:
Công thức này sẽ trả về giá trị 6,792, là đường kính của Sao Hỏa vì hàm INDEX lấy giá trị ở hàng thứ 4, cột thứ 2 trong phạm vi đã chỉ định.
Tóm lại:
- Hàm INDEX giúp bạn lấy giá trị từ một vị trí nhất định trong phạm vi dữ liệu dựa trên chỉ số hàng và cột.
- Khi phạm vi là một chiều, bạn chỉ cần chỉ định số hàng.
- Khi phạm vi là hai chiều, bạn cần chỉ định cả số hàng và số cột.
Điều này rất hữu ích khi bạn cần lấy dữ liệu từ các bảng lớn mà không cần phải biết trước vị trí chính xác của dữ liệu đó.
3. Kết Hợp Hàm MATCH và INDEX trong Excel
3. Sử Dụng Hàm INDEX và MATCH Kết Hợp Trong Excel
Khi bạn muốn kết hợp hàm INDEX và MATCH trong Excel. Bạn có thể tạo ra các công thức mạnh mẽ để tìm kiếm và truy xuất dữ liệu một cách linh hoạt hơn. Hãy xem ví dụ dưới đây để hiểu rõ hơn.
3.1. Công thức
INDEX(array;MATCH(Lookup_value;Lookup_array;match_type);column_num)
Ví dụ: Bảng Doanh Số Bán Hàng
Giả sử bạn có bảng dữ liệu về doanh số bán hàng của nhân viên trong ba tháng (Tháng 1, Tháng 2, Tháng 3). Bạn muốn tìm doanh số bán hàng trong tháng 2 cho nhân viên “Frantz”, bạn cần sử dụng cả INDEX và MATCH.
Ở đây, H2 chứa “Frantz”. Công thức này vẫn trả về $5,194.
Tóm Lại:
- INDEX cần một vị trí để trả về giá trị.
- MATCH tìm vị trí đó dựa trên giá trị cụ thể.
- Bạn có thể lồng ghép INDEX vào MATCH để tạo công thức tìm kiếm linh hoạt hơn.
3.2.1 Công thức
=INDEX(array;MATCH(Lookup_value;Lookup_array;match_type);MATCH(Lookup_value;Lookup_array;match_type))
Ví dụ Tìm Doanh Số Bán Hàng; Giả sử bạn có bảng doanh số bán hàng theo tháng cho từng nhân viên. Để tìm doanh số bán hàng của một nhân viên cụ thể trong một tháng cụ thể, bạn cần kết hợp cả hai hàm.Khi bạn muốn tìm doanh số của “Frantz” trong “Tháng 3”. Bạn cần một công thức sử dụng cả hai hàm INDEX và MATCH:
- C3:E11: Phạm vi dữ liệu.
- MATCH(H2; B3:B11; 0): Tìm vị trí hàng của “Frantz” trong cột tên.
- MATCH(H3; C2:E2; 0): Tìm vị trí cột của “Tháng 3”.
- Công thức này trả về $10,525 , là doanh số bán hàng của “Frantz” trong “Tháng 3”.
3.3 Tra cứu bên trái
Một trong những lợi thế chính của INDEX và MATCH so với hàm VLOOKUP là khả năng thực hiện “tra cứu bên trái”. Nói một cách đơn giản, điều này chỉ có nghĩa là tra cứu trong đó cột ID nằm bên phải các giá trị bạn muốn truy xuất
3.3.1 Công thức
INDEX(array:MATCH(Lookup_value;Lookup_array;match_type))
4. Một số lỗi thường gặp khi sử dụng hàm MATCH và INDEX trong Excel
sử dụng hàm INDEX kết hợp với hàm MATCH, bạn sẽ gặp lỗi #NA và lỗi #VALUE xuất hiện. Các nguyên nhân gây ra lỗi này thường là:
- Lỗi #VALUE thường xuất hiện nếu bạn sử dụng công thức mảng mà không nhấn Ctrl + Shift + Enter để xác nhận nó là công thức mảng. Nếu không làm điều này, Excel sẽ không thể xử lý công thức đúng cách và trả về lỗi #VALUE.
- Điều này xảy ra khi bạn đang cố gắng tìm một giá trị phù hợp. Nhưng Excel không nhận diện công thức của bạn như một công thức mảng. Từ đó, dẫn đến việc không thể thực hiện phép tính chính xác.
- Lỗi #N/A xảy ra khi hàm MATCH không tìm thấy giá trị bạn đang tìm kiếm trong phạm vi dữ liệu. Điều này có nghĩa là giá trị đó không tồn tại trong dữ liệu của bạn. Nếu bạn đang sử dụng hàm INDEX và MATCH theo dạng mảng (nhiều ô). Bạn cần nhấn Ctrl + Shift + Enter để Excel nhận diện đó là công thức mảng. Nếu không, bạn sẽ nhận được lỗi.Khi sử dụng MATCH, bạn cần đảm bảo rằng giá trị trong đối số
match_typephù hợp với cách sắp xếp dữ liệu. Ví dụ, nếu bạn đang tìm giá trị khớp chính xác, nhưng dữ liệu không được sắp xếp đúng cách, lỗi #N/A sẽ xuất hiện.
Pro tip: Hướng dẫn hàm INDEX và MATCH trong Excel chi tiết từ Microsoft
Tạm kết
Hiểu rõ về cách sử dụng và nguyên tắc hoạt động của Hàm INDEX và MATCH. Nó sẽ giúp bạn thuận lợi hơn trong quá trình xử lí dữ liệu. Nếu bạn đang quan tâm về phân tích dữ liệu và muốn tìm hiểu sâu hơn về các khía cạnh quan trọng trong lĩnh vực này. Hãy đọc thêm bài viết trên Blog: Data Analyst cần học gì của ACE Academy. Bài viết này sẽ giúp bạn hiểu rõ hơn về Data Analytics và lộ trình học chuẩn chỉnh. Bên cạnh đó còn có các phương pháp, kỹ năng cần thiết cho một nhà phân tích dữ liệu chuyên nghiệp. Hy vọng bài viết về hàm các phép tính cơ bản trong Excel của ACE Academy sẽ đem đến bạn kiến thức hữu dụng và có thể áp dụng chúng vào học tập và công việc một cách hiệu quả.
Ngoài ra, nếu bạn muốn trau dồi kỹ năng xử lý dữ liệu, học cách xây dựng các Dashboard động và phát triển nền tảng vững chắc, nâng cao khả năng trực quan hoá dữ liệu và truyền đạt thông tin một cách rõ ràng và thuyết phục với Excel. Hãy tham khảo khoá học Excel Business & Finance tại ACE Academy!
Đăng kí học thử khóa học về Excel Business & Finance tại đây
