Học Excel Online đi sâu vào cách sử dụng phối kết hợp hàm index và match. Cho mình khả năng tìm kiếm kiếm nhiều điều kiện tương tự như trả về những kết quả


INDEX/MATCH dùng như thế nào?

Cú pháp thường xuyên gặp, hay sử dụng với VLOOKUP:

Nếu cùng với VLOOKUP, ta gồm công thức như sau:=VLOOKUP(Giá trị dò tìm, Vùng tài liệu , sản phẩm tự cột trả về, Tìm thiết yếu xác/gần đúng)

Ta bao gồm ví dụ sau, với hàm VLOOKUP các bạn lưu ý những vấn đề sau:

*

Giá trị dò kiếm tìm là “Huỳnh Văn Vê“, gồm trong cột vùng tự A1:A8, và cột đựng đó luôn nằm bên trái quanh đó cùng vùng dữ liệu (A1:C8).Vùng dữ liệu: A1:C8, khi chúng ta quét vùng dữ liệu luôn luôn nhớ, bọn họ sẽ phải thắt chặt và cố định bằng phím F4, trước lúc làm bất kỳ việc gì tiếp theo: $A$1:$C$8. Do sao? vì khi họ kéo công thức, vùng tham chiếu tài liệu sẽ luôn được cầm định.Cột trả về, ta đếm theo đồ vật tự tự trái quý phái phải, tính trường đoản cú cột chứa giá trị dò tìm. Ở ví dụ bên dưới là cột lắp thêm 3.Luôn luôn luôn nhớ, nhập số 0 sau cuối ở hàm VLOOKUP với hàm MATCH. Tại sao? vì số 0 tương xứng với FALSE, là tìm kiếm thiết yếu xác. Luôn luôn luôn tra cứu kiếm chủ yếu xác. Trên sao không tìm tương đối? Có một số trường hợp chúng ta sẽ tìm kiếm tương đối, sát đúng, nhưng đó là vài ngôi trường hợp khi chúng ta đã chũm rõ.

Bạn đang xem: Hàm dò tìm nhiều điều kiện

Và bây giờ chúng ta có công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Cùng với E2 là giá bán trị nên tìm, vào vùng tài liệu từ A1:C8, và dấu $ mang ý nghĩa sâu sắc cố định vùng tài liệu tìm kiếm nhằm khi kéo bí quyết vùng sẽ cụ định. Cột dữ liệu trả về là cột đồ vật 3, tính từ vị trí đếm tự cột đựng giá trị dò tra cứu sang bên phải. Cùng số 0, là tìm thiết yếu xác, luôn luôn là số 0.

Vậy cùng với INDEX thì sẽ đổi khác như nắm nào:=INDEX(Cột kết quả, MATCH(Giá trị dò tìm, Cột dò tìm đựng giá trị đề xuất tìm, Tìm chủ yếu xác/gần đúng)


Dù đã gồm phần mềm, nhưng năng lực Excel vẫn rất là quan trọng cùng với kế toán, bạn đã vững vàng Excel chưa? Hãy nhằm tôi góp bạn, đk khoá học Excel:


*


*
Ví dụ 1: VLOOKUP vs INDEX/MATCH

Lúc này các bạn sẽ thấy hàm có cú pháp như sau: =INDEX(Vùng kết quả, Dòng, Cột).

Vùng kết quả: $C$1:$C$8, khác với vùng tài liệu của hàm VLOOKUP, hôm nay ta chỉ chọn mỗi vùng dữ liệu cột Điểm thi thay vị cả cột cất giá trị dò tìm.Hàm MATCH(Giá trị dò tìm, Vùng tìm kiếm kiếm, Tìm bao gồm xác/tương đối). Hôm nay E3 là quý hiếm dò tìm, với vùng tìm kiếm chỉ là đúng vùng cột đựng giá trị dò tìm: A1:A8, tương tự như VLOOKUP, vùng tìm kiếm luôn phải cố định và thắt chặt vùng $A$1:$A$8. Và luôn luôn luôn tìm thiết yếu xác, là số 0 hoặc FALSE.Với phương pháp trên, ta thấy hàm MATCH đang trả về quý giá là 2, khớp ứng dòng kiếm tìm thấy từ trên xuống. INDEX($C$1:$C$8, 2) => tác dụng là 7.

Vì sao lại nói sử dụng INDEX/MATCH dễ dàng hơn VLOOKUP?

VLOOKUP đòi hỏi cột chứa giá trị dò tìm đề xuất nằm không tính cùng phía bên trái vùng dữ liệu. Nếu nằm bên phải thì lúc này phải dùng hàm mảng kết phù hợp với hàm CHOOSE để lấy kết quả tương ứng. Vậy thuộc xem lại ví dụ, các bạn chỉ vấn đề quét vùng lựa chọn cột kết quả, tra cứu trong cột đựng giá trị dò tìm. Gắng là xong!

*
Ví dụ 2: VLOOKUP phối kết hợp CHOOSE nhằm dò tìm ngược

Ngược lại với lấy ví dụ như trước đó, họ có cột Lớp nằm ngoại trừ cùng mặt trái, và vấn đề là từ tên học tập viên, bọn họ sẽ tìm ra lớp của học viên đó. Các bạn sẽ viết hàm VLOOKUP theo như thông thường thế nào? nghĩ về xem nhé? Vậy với hàm VLOOKUP, các bạn phải dùng phối kết hợp hàm CHOOSE, với cú pháp =CHOOSE(1,2, Cột cất giá trị dò tìm, Cột Kết quả).

Vậy ta gồm cú pháp bao quát như sau: =VLOOKUP(Giá trị dò tìm, CHOOSE(1,2, Cột chứa giá trị dò tìm, Cột kết quả), Cột trả về<2>, Tìm đúng đắn <0>)

Nếu dấu phân cách của công ty là dấu chấm phẩy, thì bí quyết sẽ là: =VLOOKUP(Giá trị dò tìm; CHOOSE(1 2; Cột đựng giá trị dò tìm; Cột kết quả); Cột trả về<2>; Tìm chính xác <0>)

Với INDEX/MATCH thì các bạn thấy vẫn như lấy một ví dụ 1, =INDEX(Vùng kết quả, MATCH(Giá trị dò tìm, Vùng dò tìm, Tìm bao gồm xác). Đơn giản rồi nên không nào?

Dùng VLOOKUP/CHOOSE hoặc INDEX/MATCH tìm theo khá nhiều điều kiện

*

Ta có ví như trên, bây giờ có 2 chúng ta “Nguyễn Thị Đét” thuộc tên học tập 2 lớp khác nhau, tương tự với 2 điều kiện để họ tìm ra điểm thi của từng bạn. Vậy làm cố nào nhằm tìm ra? vẫn chính là hàm VLOOKUP/CHOOSE, hôm nay bạn phải ghép 2 đk với nhau bởi dấu & (dấu “and”/”và”), thuộc với câu hỏi ghép 2 cột cất giá trị dò tìm với nhau cũng tương tự như dấu &. Ta tất cả cú pháp như sau:

=VLOOKUP(&&, CHOOSE(1, 2, &&, ), 2 là Cột trả về, 0 là Tìm chủ yếu xác)

Và đây là công thức mảng, đòi hỏi chúng ta phải thừa nhận CTRL+SHIFT+ENTER, thay vày Enter (trả về #NA), lúc này các bạn sẽ thấy tất cả móc sừng trâu mở ra trong công thức.

Tại sao lại MATCH thời gian tìm 1, thời điểm lại là TRUE và lúc nào đề nghị nhấn CTRL+SHIFT+ENTER?

Ví dụ 3: INDEX/MATCH kiếm tìm kiếm theo khá nhiều điều kiện

Với INDEX/MATCH, chúng ta có cú pháp như sau:

=INDEX(Vùng kết quả, MATCH(1,(=)*(=*(=),0)

Và đây là công thức mảng, cần phải bao gồm nhấn CTRL+SHIFT+ENTER. Do sao lúc lại là 1, thời gian lại TRUE? khi bạn chỉ có một biểu thức, lúc này kết quả vẫn trả về TRUE/FALSE, khi gồm 2 biểu thức TRUE*TRUE, Excel sẽ đưa TRUE thành 1*1 = 1.

Giá trị dò tra cứu là TRUE, khi gồm một biểu thức: (Biểu thức so sánh) => (=)Giá trị dò tra cứu là 1, khi bao gồm hai biểu thức trở lên: (Biểu thức 1)*(Biểu thức 2) => (=)*(=)*(=)Tìm FALSE thời gian nào? khi bạn cần tìm quý hiếm không thỏa theo biểu thức so sánh của mình.

Dùng INDEX/MATCH phối kết hợp INDEX để đổi khác công thức mảng thành công thức bình thường.

Xem thêm: Hơn 100 Lời Tỏ Tình Hay Nhất, Lời Tỏ Tình Dễ Thương Ngắn Gọn

Trong ví dụ 3, các bạn làm thân quen với bí quyết mảng, đòi hỏi thao tác nên nhấn CTRL+SHIFT+ENTER, để tránh câu hỏi phải làm thao tác này, bạn cũng có thể kết đúng theo thêm hàm INDEX bên phía trong hàm MATCH để trả về giá bán trị thứ nhất trong danh sách MATCH tra cứu thấy.

Ví dụ 4: INDEX/MATCH dò tìm theo nhiều điều kiện

Với cú pháp từ ô G7, G4 trong ví dụ trên ta có:

=INDEX(Vùng kết quả, MATCH(TRUE, INDEX(Biểu thức, 0), 0))=INDEX(Vùng kết quả, MATCH(1, INDEX((Biểu thức 1)*(Biểu thức 2), 0), 0))Lưu ý, luôn luôn có 2 loại số “, 0), 0)”, số 0 đầu tiên cho hàm INDEX(Biểu thức,0). Số 0 sau cùng cho hàm MATCH(,,0).

Làm báo cáo chi tiết, trích lọc hóa đối chọi với INDEX phối hợp COUNTIFS. Công dụng trả về nhiều tác dụng từ một hoặc những điều kiện.

*
Ví dụ 5: search kiếm trả về những kết quả

Với quý giá dò tra cứu “Nguyễn Thị Đét” bạn có tương đối nhiều kết quả trả về, vậy gồm cách như thế nào liệt kê được vớ cả công dụng không? Câu vấn đáp là có. Với cú pháp (0=COUNTIFS(<$<Ô đầu tiên trả về kết quả>:<Ô thứ nhất trả về kết quả>>, Vùng kết quả), trong ví dụ: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Xét thêm đk đã trả về công dụng trước đó hay chưa? Nếu sẽ trả về kết quả rồi, thì thải trừ để lấy chiếc tiếp theo. Hôm nay ta đang có kết quả mong muốn.

Kết quả trả về #NA là không kiếm thấy nữa, để không hiển thị lỗi, chúng ta cũng có thể dùng hàm IFERROR(Công thức, “”).Biểu thức điều kiện theo thương hiệu đầu tiên, bản thân cần cố định cả ô $E$2, để khi kéo công thức xuống sẽ cố định ô giá trị dò tìm.$F$1:F1, bởi vì sao chỉ cố định và thắt chặt cái đầu tiên, bởi để lúc kéo xuống bên dưới, nó sẽ đổi thay $F$1:F<2->n>.

Làm vậy nào để in phiếu xuất kho có khá nhiều sản phẩm? cùng một phiếu, có tương đối nhiều mã thành phầm khác nhau

*

Ví dụ với 1 mã xuất kho, bạn sẽ xuất ra nhiều thành phầm khác nhau. Hôm nay in phiếu xuất kho, chúng ta chỉ câu hỏi nhập mã phiếu xuất kho, đã trả về danh sách thành phầm tương ứng.

Ta lập cột phụ tham chiếu theo mã phiếu xuất kho, hôm nay COUNTIFS làm nhiệm vụ đánh số thiết bị tự giúp chúng ta. Vẫn luôn là cột dây vào 1 đầu cột, dây còn sót lại thả thoải mái để diều cất cánh cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), và $E$9 là giá trị dò tìm, cũng phải cố định và thắt chặt để khi kéo xuống chúng ta không biến đổi điều kiện tra cứu kiếm. Lúc này các bạn sẽ thấy số tăng cao theo vùng Mã phiếu xuất kho, nếu không tìm kiếm thấy nữa, thì chỉ là lặp lại cái sau cùng tìm thấy.

Lúc này trở về Sheet Phiếu Xuất Kho nhằm in ấn, chỉ vấn đề lập công thức tương ứng như sau, mình phân tích và lý giải từ trái sang:

Sản phẩm: =INDEX(Vùng tác dụng < các cột>, MATCH(ROW(1:1), Vùng cột phụ,0), Cột trả về). Ta tất cả Vùng kết quả là B1:D6, lúc này chúng ta cũng có thể vận dụng chỉ cột B1:B6 cũng được, nhưng chúng ta cũng có thể trả về cột tương ứng ta hy vọng muốn. Ở trên đây ta bao gồm vùng B1:D6, cột trả về là một trong những = cột B. Còn ROW(1:1) là gì? ROW(1:1) trả về 1. Và khi bọn họ kéo công thức xuống B13, nó thay đổi ROW(2:2) = 2.Số lượng: Ở đây chúng ta thấy chỉ khác mỗi Cột trả về đúng không? vì sao lại là COLUMN(B1)? Hàm COLUMN(B1) đã trả về kết quả cột B1 là cột bao nhiêu, có nghĩa là 2. Lúc kéo sang phải, nó biến hóa COLUMN(C1), có nghĩa là 3. Vậy lúc kéo quý phái trái khu vực cột sản phẩm thì nó thành gì? các bạn đoán xem? Là COLUMN(A1), có nghĩa là 1. Giờ đồng hồ thì bạn hiểu vày sao mình để công thức cho chúng ta thấy rồi nên không?Chỗ #NA của STT và Sản phẩm, mình cố tình để công dụng như vậy, nếu bạn có nhu cầu không hiển thị #NA, hãy cần sử dụng IFERROR theo cột số lượng và Kho nhé!

Để bài viết liên quan các bạn có thể sử dụng công dụng tìm kiếm trên web trên ô search kiếm, hoặc tìm kiếm kiếm với Google, hãy thêm từ khóa “facestock.vn” + “từ khóa”. Ví dụ: “facestock.vn”,”INDEX/MATCH”.