Nếu hàm index và match được kết hợp với nhau sẽ là một công cụ hữu ích và tốt hơn rất nhiều so với việc sử dụng hàm Vlookup trong Excel. Tại sao lại như vậy? Theo dõi bài viết dưới đây để tìm hiểu kỹ hơn nhé!

Nếu hàm index trong Excel và hàm match kết hợp với nhau sẽ là một cánh tay phải đắc lực giúp ích cho bạn rất nhiều trong công việc, giúp cho công việc của bạn được diễn ra nhanh chóng và chính xác hơn, trong khi đó hàm Vlookup trong Excel lại khó có thể làm được điều này. Ngoài ra, hàm index và match rất linh hoạt và có thể được áp dụng cho nhiều trường hợp khác nhau. Theo dõi bài viết dưới đây để được 123job bật mí nhiều thông tin hữu ích liên quan đến hàm index và match có thể bạn chưa biết nhé!

I. Vì sao dùng hàm Index và Match tốt hơn dùng Vlookup trong Excel

1. Vlookup là công thức 1 chiều

Với hàm Vlookup, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải còn nếu đối với hàm index và match sẽ làm được điều này.

Vì sao dùng hàm Index và Match tốt hơn dùng Vlookup trong Excel

Vì sao dùng hàm Index và Match tốt hơn dùng Vlookup trong Excel

2. 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, chúng ta 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 một 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 còn được đúng nữa. Đối với hàm index và match thì điều này không xảy ra vì khi thêm hoặc bớt cột thì công thức trong hàm index và match sẽ được điều chỉnh theo.

3. Vlookup sẽ gặp khó khăn khi tra cứu 2 chiều

Vlookup sẽ gặp khó khăn khi tra cứu 2 chiều

Vlookup sẽ gặp khó khăn khi tra cứu 2 chiều

Ở trong ví dụ trên, nếu chúng ta muốn tra cứu dân số của Seoul vào năm 2001, lúc này chúng ta có thể dùng hàm VLOOKUP nhưng đây là một cách khó khăn, nếu dùng hàm index và match sẽ nhanh gọn hơn như sau:

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

  • Hàm Match đầu tiên cho biết thủ đô Seoul của Hàn Quốc nằm ở dòng thứ mấy.
  • Hàm Match thứ hai cho biết dữ liệu dân số của năm 2001 nằm ở cột nào.
  • Hàm index trong excel 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 biết.

II. Cách kết hợp hàm Index và Match trong Excel

1. Hàm Index trong Excel

Hàm Index trong Excel 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 một ô dữ liệu có chỉ số hàng và 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 đó có:

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

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

  • Reference: Vùng tham chiếu chứa những 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.

2. Hàm Match trong Excel

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

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

3. Kết hợp hàm index và match

Để hiểu rõ hơn về tác dụng của việc kết hợp hàm index và match thì chúng ta cùng nhau làm ví dụ dưới đây:

Ví dụ: Kết hợp hàm index và match tìm email theo tên

Khi nhìn vào bảng trên, ta thấy ở vị trí dòng thứ 3, trong cột Email sẽ xuất hiện Email cần tìm. Như vậy chỉ cần dóng theo cột Email, tại vị trí ở dòng 3 trong bảng là chúng ta sẽ có được kết quả cần tìm. Điều đó chính là cách kết hợp giữa hàm index và match:

Kết hợp hàm index và match

Kết hợp hàm index và match

Trong công thức tại ô D15 tìm Email, chúng ta có thể thấy:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX trong Excel có nhiệm vụ như sau:

  • Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
  • Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH ở trên
  • Giao điểm giữa dòng và cột trong hàm INDEX là kết quả email cần tìm.

III. Làm thế nào để sửa lỗi #N/A trong các hàm Index/Match

Làm thế nào để sửa lỗi #N/A trong các hàm Index/Match luôn là câu hỏi băn khoăn, thắc mắc của nhiều người. Thấu hiểu được những nỗi lo đó, dưới đây là cách sửa lỗi #N/A trong các hàm Index/Match mà 123job muốn chia sẻ tới bạn đọc:

1. Vấn đề: không có dữ liệu nào khớp với

Khi hàm match không tìm thấy giá trị tra cứu ở trong mảng tra cứu thì chắc chắn một điều là nó sẽ trả về lỗi #N/A. Nếu bạn cho rằng dữ liệu sẽ hiện diện ở trong bảng tính nhưng khớp không thể xác định được vị trí đó, có thể là do 2 nguyên nhân chính dưới đây:

  • Ô có chứa các ký tự không mong muốn hoặc dấu cách đã bị ẩn.
  • Ô có thể không được định dạng là kiểu dữ liệu chính xác, ví dụ như ô có giá trị số, nhưng có thể sẽ được định dạng dưới dạng văn bản.

Giải pháp: Để loại bỏ được các ký tự không mong muốn hoặc dấu cách bị ẩn, bạn hãy dùng hàm Clean hoặc Trim tương ứng. Ngoài ra, hãy kiểm tra xem các ô đã được định dạng như kiểu dữ liệu chính xác hay không.

2. Bạn đã sử dụng một công thức mảng mà không cần nhấn Ctrl + Shift + Enter

Khi bạn sử dụng một mảng trong chỉ mục, khớp hoặc kết hợp cả hai hàm đó lại với nhau, cần phải nhấn đồng thời Ctrl + Shift + Enter ở trên bàn phím. Lúc này Excel sẽ tự động đính kèm công thức trong dấu ngoặc nhọn {}. Nếu bạn tìm cách nhập dấu ngoặc đơn thì Excel sẽ hiển thị công thức ở dưới dạng văn bản.

Làm thế nào để sửa lỗi #N/A trong các hàm Index/Match

Làm thế nào để sửa lỗi #N/A trong các hàm Index/Match

3. Vấn đề: Có một tính không nhất quán trong loại kết hợp và thứ tự sắp xếp của dữ liệu

Khi bạn sử dụng khớp thì nên có một sự nhất quán giữa các giá trị trong đối số match_type và thứ tự được sắp xếp của các giá trị trong mảng tra cứu. Nếu cú pháp deviates từ các quy tắc sau đây, bạn sẽ thấy lỗi #N/A xuất hiện.

  • Nếu match_type có giá trị là 1 hoặc không xác định được thì các giá trị trong lookup_array phải theo một thứ tự tăng dần. Ví dụ như :-2,-1, 0, 1, 2,3... A, B, C... FALSE, TRUE, để đặt tên một vài.
  • Nếu match_type có giá trị là -1, các giá trị trong lookup_array sẽ phải theo thứ tự giảm dần.

IV. Kết luận

Như vậy chúng ta có thể thấy cách sử dụng hàm Index và Match để tìm kiếm dữ liệu trong Excel là điều không quá khó khăn và đặt ra nhiều thách thức. Bên cạnh đó với những vai trò to lớn mà nó mang lại thì chúng ta nên áp dụng hàm index và match nhiều hơn nữa để áp dụng cho công việc của mình. Trên đây là toàn bộ thông tin về hàm index và match, cách để sửa lỗi #N/A trong các hàm Index/Match mà 123job muốn chia sẻ tới bạn đọc. Hy vọng với những chia sẻ trên mang tới cho bạn nhiều thông tin bổ ích về các hàm trong Excel.