INDEX/MATCH – Cách thực hiện tìm kiếm với nhiều tiêu chí và xuất ra nhiều kết quả

Siêu Marketing giới thiệu cách áp dụng hàm index và match phối hợp nhau. Điều này mang lại khả năng tìm kiếm theo nhiều tiêu chí cũng như xuất nhiều kết quả

Làm thế nào để dùng INDEX/MATCH?

  • Cú pháp thường thấy này, thường dùng với VLOOKUP:

Với VLOOKUP, chúng ta sử dụng công thức dưới đây:
=VLOOKUP(Giá trị tìm kiếm, Phạm vi dữ liệu [bao gồm: Cột tìm kiếm + Cột trả về], Số thứ tự cột cần trả kết quả, Tìm đúng/không đúng)

Dưới đây là ví dụ với VLOOKUP, các bạn cần chú ý các điều sau:

  • Chúng ta có giá trị tìm kiếm “Huỳnh Văn Vê“, nằm ở cột từ A1:A8, và cột này luôn ở phía bên trái cùng của phạm vi dữ liệu (A1:C8).
  • Phạm vi dữ liệu là A1:C8, khi scan phạm vi này, bạn cần nhớ fix cố định bằng F4, trở thành $A$1:$C$8. Điều này quan trọng như thế nào? Bởi vì khi bạn kéo dãn công thức, phạm vi này sẽ không thay đổi.
  • Đối với cột kết quả, mình sẽ đếm từ vị trí cột chứa giá trị tìm kiếm về phía bên phải. Trong trường hợp này, cột kết quả là cột thứ 3.
  • Không bao giờ quên điền số 0 ở cuối cú pháp của VLOOKUP và MATCH. Tại sao vậy? Bởi vì số 0 tượng trưng cho FALSE, để tìm kiếm chính xác. Ta luôn muốn tìm kiếm chính xác. Vậy tại sao không tìm gần đúng? Cũng có trường hợp bạn cần tìm kiếm gần đúng, nhưng chỉ khi bạn rõ ràng về nó.

Đọc thêm: Sử dụng hàm index và match với nhiều điều kiện

Bây giờ, chúng ta có công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Trong đó E2 chính là giá trị mà bạn muốn tìm, trong phạm vi từ A1:C8, và dấu $ để fix phạm vi này khi kéo dãn công thức. Cột thứ 3 chính là vị trí cần trả kết quả, và số 0 tượng trưng cho việc tìm chính xác.

Vậy khi sử dụng INDEX, công thức sẽ thay đổi ra sao:
=INDEX(Cột kết quả, MATCH(Giá trị tìm kiếm, Cột tìm kiếm, Tìm đúng/không đúng)

Hình 1: VLOOKUP so với INDEX/MATCH

Ở đây, bạn sẽ nhận thấy cú pháp hàm như sau: =INDEX(Phạm vi kết quả, Hàng, Cột).

  • Phạm vi kết quả: $C$1:$C$8, này khác hẳn với phạm vi dữ liệu của VLOOKUP, ở đây ta chỉ quan tâm đến phạm vi cột Điểm thi, không bao gồm cột chứa giá trị tìm kiếm.
  • Hàm MATCH(Giá trị tìm kiếm, Phạm vi tìm kiếm, Tìm chính xác/không chính xác). Tại đây E3 là giá trị bạn muốn tìm, và phạm vi tìm kiếm chỉ cần chính xác cột chứa giá trị tìm kiếm: A1:A8, cũng giống VLOOKUP, phạm vi tìm kiếm cần phải fix $A$1:$A$8. Và chúng ta vẫn luôn tìm chính xác, sử dụng số 0 hoặc FALSE.
  • Với có công thức được nêu, hàm MATCH sẽ trả về giá trị là 2, thể hiện hàng mà giá trị tìm kiếm được tìm thấy. INDEX($C$1:$C$8, 2) => Kết quả trả về là 7.

Tại sao lại cho rằng dùng INDEX/MATCH dễ hơn VLOOKUP?

VLOOKUP yêu cầu cột chứa giá trị bạn muốn tìm phải nằm ở cực trái của phạm vi dữ liệu. Nếu không phải như vậy, ta phải dùng hàm mảng kết hợp cùng hàm CHOOSE để lấy kết quả chính xác. Cùng xem xét lại ví dụ, bạn chỉ cần chọn phạm vi cột kết quả và tìm trong cột chứa giá trị mà bạn muốn tìm. Chỉ thế thôi!

Hình ảnh minh họa 2: Cách dùng VLOOKUP cùng với CHOOSE để tra cứu ngược

Trái với tình huống đã trình bày, giả sử cột Lớp đặt bên cạnh trái cùng của bảng, và ta cần xác định lớp học dựa vào tên của Học viên. Bạn sẽ áp dụng hàm VLOOKUP một cách thông thường ra sao? Hãy cân nhắc thử. Đối với trường hợp này, cần phải sử dụng hàm CHOOSE kết hợp với VLOOKUP, cú pháp là =CHOOSE({1,2}, Cột chứa thông tin cần tìm, Cột cho kết quả).

Cú pháp chung cho việc này sẽ là: =VLOOKUP(Giá trị cần tìm kiếm, CHOOSE({1,2}, Cột có thông tin cần tìm, Cột trả kết quả), Cột cần trả về[2], Tìm kiếm chính xác [0])

Nếu bạn dùng dấu phẩy làm dấu phân cách, công thức sẽ được viết như sau: =VLOOKUP(Giá trị cần tìm kiếm; CHOOSE({1,2}; Cột có thông tin cần tìm; Cột trả kết quả); Cột cần trả về[2]; Tìm kiếm chính xác [0])

Khi tiến hành với INDEX/MATCH, mọi thứ trở nên giống với ví dụ đầu tiên, =INDEX(Khu vực cho ra kết quả, MATCH(Giá trị cần tìm kiếm, Vùng tìm kiếm, Tìm kiếm chính xác). Quá trình này khá là đơn giản, phải không nào?

Áp dụng VLOOKUP/CHOOSE hay INDEX/MATCH để tìm kiếm dựa trên nhiều điều kiện

Từ ví dụ hiển thị trên, chúng ta gặp tình huống có 2 người mang tên “Nguyễn Thị Đét” học ở 2 lớp khác biệt, tạo nên 2 điều kiện cần thiết để tìm điểm thi của họ. Cách thức để giải quyết là gì? Sử dụng hàm VLOOKUP/CHOOSE, lúc này bạn cần kết hợp hai điều kiện lại với sự hỗ trợ của dấu & (nghĩa là “and”/”và”), và cũng cần ghép hai cột chứa dữ liệu cần tìm với nhau bằng dấu &. Cú pháp sẽ ra như sau:

=VLOOKUP([Giá trị tìm A]&[Giá trị tìm B]&[Giá trị tìm n], CHOOSE({1,2}, [Cột chứa giá trị A]&[Cột chứa giá trị B]&[Cột chứa giá trị n], [Cột trả kết quả]), 2 là cột trả về, 0 cho tìm chính xác)

Và đây là công thức mảng, yêu cầu bạn phải nhấn CTRL+SHIFT+ENTER thay vì chỉ Enter (nếu không sẽ ra #NA), khi đó bạn sẽ thấy dấu ngoặc móc xuất hiện xung quanh công thức.

Vì sao khi sử dụng MATCH, đôi khi tìm theo giá trị 1, khi khác lại là TRUE và khi nào cần dùng CTRL+SHIFT+ENTER?

Hình ảnh minh họa 3: Cách dùng INDEX/MATCH để tìm kiếm dựa trên nhiều tiêu chí

Với lệnh INDEX/MATCH, chúng ta được cung cấp cú pháp sau:

=INDEX(Khu vực kết quả, MATCH(1, ([Giá trị tìm A]=[Cột giá trị A])*([Giá trị tìm B]=[Cột giá trị B])*([Giá trị tìm n]=[Cột giá trị n]), 0))

Đây cũng là công thức mảng, đòi hỏi phải sử dụng tổ hợp phím CTRL+SHIFT+ENTER. Tại sao đôi khi tìm giá trị là 1, lúc lại là TRUE? Khi chỉ có một biểu thức, kết quả sẽ là TRUE hoặc FALSE. Nếu có hai biểu thức và cả hai đều TRUE (TRUE*TRUE), Excel sẽ quy TRUE thành 1 và như vậy 1*1 = 1.

  • Giá trị tìm kiếm là TRUE nếu chỉ có một biểu thức so sánh: (So sánh) => ([Giá trị A]=[Cột có giá trị A])
  • Giá trị tìm kiếm là 1 nếu có hai biểu thức so sánh trở lên: (So sánh 1)*(So sánh 2) => ([Giá trị A]=[Cột có giá trị A])*([Giá trị B]=[Cột có giá trị B])*([Giá trị n]=[Cột có giá trị n])
  • Khi nào cần tìm FALSE? Khi bạn muốn tìm giá trị không phù hợp với biểu thức so sánh đã đặt ra.

Cách kết hợp INDEX/MATCH và INDEX để chuyển đổi từ công thức mảng thành công thức thông thường.

Qua ví dụ thứ ba, bạn đã được làm quen với công thức mảng, đòi hỏi phải thực hiện thao tác nhập phức tạp hơn.

Bạn có thể ngăn cần thiết phải bấm tổ hợp phím CTRL+SHIFT+ENTER bằng cách tích hợp hàm INDEX vào trong hàm MATCH, nhằm mục đích trả về kết quả tìm kiếm đầu tiên của danh sách mà MATCH xác định được.

Hình minh họa 4: Việc sử dụng INDEX/MATCH để tìm kiếm theo nhiều tiêu chí

Trong ví dụ trên từ ô G7, G4, cú pháp có thể diễn giải như sau:

  • =INDEX(Khu vực trả kết quả, MATCH(TRUE, INDEX(Điều kiện, 0), 0))
  • =INDEX(Khu vực trả kết quả, MATCH(1, INDEX((Điều kiện thứ 1)*(Điều kiện thứ 2), 0), 0))
  • Chú ý rằng chúng ta luôn có hai chuỗi “, 0), 0)”; số 0 đầu là dành cho hàm INDEX(Điều kiện,0) còn số 0 thứ hai là dành cho hàm MATCH(,,0).

Khi cần tạo báo cáo chi tiết, lọc dữ liệu từ hóa đơn, bạn có thể sử dụng hàm INDEX phối hợp với COUNTIFS để lấy về nhiều kết quả từ một hoặc nhiều tiêu chí cụ thể.

Hình minh họa 5: Kỹ thuật tìm kiếm kết quả đa dạng

Khi dò tìm giá trị “Nguyễn Thị Đét” mà nhận được nhiều kết quả, làm thế nào để liệt kê tất cả? Câu trả lời chính là sử dụng cú pháp (0=COUNTIFS([Khu vực ô kết quả đầu tiên]:[Khu vực ô kết quả đầu tiên], Khu vực trả kết quả)), ví dụ trong trường hợp này là (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Đây chính là việc kiểm tra xem liệu kết quả đó đã từng được trả về trước đây chưa. Nếu đã trả về, thì loại bỏ nó để xác định kết quả tiếp theo. Kết quả cuối cùng sẽ là những gì bạn mong đợi.

  • Nếu nhận kết quả là #NA tức là không tìm thấy thêm, bạn có thể sử dụng hàm IFERROR(Công thức, “”) để tránh lỗi hiển thị.
  • Để cố định ô chứa giá trị tìm kiếm, ví dụ ô $E$2, bạn kéo công thức xuống mà không thay đổi điều kiện tìm kiếm.
  • Khi kéo công thức xuống phía dưới, $F$1:F1 sẽ trở thành $F$1:F[2->n] vì chỉ cố định phần đầu tiên.

Làm thế nào để chế tạo phiếu xuất kho cho các sản phẩm đa dạng? Một phiếu xuất kho có thể gồm nhiều mã sản phẩm khác nhau.

Trong trường hợp bạn có một mã xuất kho và muốn xuất ra nhiều sản phẩm khác nhau, bạn chỉ việc nhập mã phiếu xuất kho và hệ thống sẽ cung cấp danh sách các sản phẩm liên quan.

Khi xây dựng cột phụ tham chiếu theo mã phiếu xuất kho, bạn sẽ dùng hàm COUNTIFS để gán số thứ tự cho từng mục. Như trong trường hợp cột A, nối một đầu với cột, $A$2:A2 => COUNTIF($A$2:A2, $E$9), và cố định giá trị tìm kiếm $E$9 nếu bạn kéo công thức xuống. Số thứ tự sẽ tăng dần theo các mã phiếu xuất kho mà ta có, và khi không có thêm kết quả, nó chỉ đơn giản là lặp lại mã cuối cùng tìm thấy.

Bạn quay trở lại Sheet Phiếu Xuất Kho để in, thiết lập công thức tương ứng như sau, tôi sẽ giải thích từ trái sang phải:

  • Số thứ tự (STT): Nếu kết quả Sản phẩm trả về là rỗng thì sẽ hiển thị rỗng, còn không thì sẽ lấy giá trị của ô ngay bên trên cộng với 1. Hàm N() ở đây dùng để kiểm tra nếu là chữ sẽ trả về 0, còn số thì trả về giá trị của số đó. N(“STT”) => 0+1 = 1, nếu có kết quả từ sản phẩm. Tìm hiểu thêm về Hướng dẫn sử dụng hàm N trong Excel.
  • Sản phẩm: =INDEX(Khu vực kết quả [nhiều cột], MATCH(ROW(1:1), Khu vực tham chiếu cột phụ, 0), Cột mong muốn). Với khu vực kết quả là B1:D6, bạn có thể vậnChỉ việc sử dụng cột B1:B6 cũng đủ, nhưng chúng ta lại có khả năng chọn cột để trả về kết quả chúng ta mong đợi. Khu vực dữ liệu hiện tại là B1:D6 và cột sẽ trả về ở đây là số 1, đó chính là cột B. Vậy ROW(1:1) là gì? Hàm ROW(1:1) cho kết quả là 1. Khi ta kéo công thức này xuống tới ô B13, nó chuyển thành ROW(2:2) tức là 2.

    Về phần Số lượng: Phần này bạn thấy chỉ có điểm khác biệt ở Cột trả về phải không nào? Vì sao lại sử dụng COLUMN(B1)? Hàm này, COLUMN(B1), sẽ chỉ ra rằng cột B1 thuộc cột thứ mấy, tức là số 2. Khi ta kéo nó qua phải, nó sẽ thay đổi thành COLUMN(C1), nghĩa là số 3. Còn nếu ta kéo sang phía trái đến cột Sản phẩm, nó sẽ biến thành gì đây? Hãy đặt giả thuyết xem? Đó chính là COLUMN(A1), có nghĩa là số 1. Bây giờ, bạn đã hiểu lý do vì sao tôi đặt công thức ra để bạn quan sát chưa?

    Đối với kết quả #NA ở cột STT và Sản phẩm, tôi cố ý để như vậy, và nếu bạn không muốn kết quả hiện lên là #NA, hãy dùng IFERROR, giống như ta đã làm với cột Số lượng và cột Kho nhé!

    Nếu muốn khám phá thêm, các bạn có thể sử dụng chức năng tìm kiếm trên website bằng cách nhập vào ô tìm kiếm, hoặc tìm qua Google, thêm từ khóa “sieumarketing.com” cùng với các “từ khóa” cần tìm. Chẳng hạn như: “sieumarketing.com”, “INDEX/MATCH”.

    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop