Hàm tìm kiếm trả về nhiều giá trị

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

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

Cú pháp hay gặp, hay dùng với VLOOKUP:

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

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

Giá trị dò search là “Huỳnh Văn Vê“, bao gồm trong cột vùng trường đoản cú A1:A8, cùng cột chứa đó luôn luôn nằm mặt trái bên cạnh cùng vùng tài 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ớ, họ sẽ phải cố định và thắt chặt bằng phím F4, trước lúc làm ngẫu nhiên việc gì tiếp theo: $A$1:$C$8. Vày sao? vì khi chúng ta kéo công thức, vùng tham chiếu tài liệu sẽ luôn được nuốm định.Cột trả về, ta đếm theo thiết bị tự tự trái sang trọng phải, tính từ bỏ cột chứa giá trị dò tìm. Ở ví dụ dưới là cột sản phẩm công nghệ 3.Luôn luôn nhớ, nhập số 0 sau cuối ở hàm VLOOKUP cùng hàm MATCH. Tại sao? vày số 0 tương ứng với FALSE, là tìm kiếm kiếm chủ yếu xác. Luôn luôn tìm kiếm thiết yếu xác. Trên sao không kiếm tương đối? Có một trong những trường hợp họ sẽ tìm tương đối, sát đúng, nhưng đó là vài trường hợp khi chúng ta đã nắm rõ.

Bạn đang xem: Hàm tìm kiếm trả về nhiều giá trị

Và hôm nay chúng ta gồm công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Cùng với E2 là giá trị nên tìm, vào vùng dữ liệu từ A1:C8, và dấu $ mang ý nghĩa sâu sắc cố định vùng dữ liệu tìm kiếm để khi kéo cách làm vùng sẽ rứa định. Cột tài liệu trả về là cột trang bị 3, tính từ địa điểm đếm trường đoản cú cột đựng giá trị dò tìm sang mặt phải. Cùng số 0, là tìm chủ yếu xác, luôn 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 cất giá trị buộc phải tìm, Tìm bao gồm xác/gần đúng)

Dù đã tất cả phần mềm, nhưng kỹ năng Excel vẫn cực kì quan trọng với kế toán, các bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học tập Excel:

Ví dụ 1: VLOOKUP vs INDEX/MATCH

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

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

VLOOKUP yên cầu cột đựng giá trị dò tìm bắt buộc nằm xung quanh cùng phía trái vùng dữ liệu. Nếu nằm bên cạnh phải thì từ bây giờ phải sử dụng hàm mảng kết phù hợp với hàm CHOOSE nhằm lấy kết quả tương ứng. Vậy thuộc xem lại ví dụ, các bạn chỉ câu hỏi quét vùng chọn cột kết quả, kiếm tìm trong cột chứa giá trị dò tìm. Nỗ lực là xong!

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

Ngược lại với ví dụ như trước đó, bọn họ có cột Lớp nằm bên cạnh cùng bên trái, và bài toán là từ tên học viên, bọn họ sẽ tìm thấy lớp của học tập viên đó. Các bạn sẽ viết hàm VLOOKUP theo như thường thì thế nào? nghĩ về xem nhé? Vậy cùng 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 đựng giá trị dò tìm, Cột Kết quả).

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

Nếu vệt phân cách của chúng ta là dấu chấm phẩy, thì công thức 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 đúng mực <0>)

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

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

Ta có ví như trên, lúc này có 2 các bạn “Nguyễn Thị Đét” thuộc tên học 2 lớp khác nhau, tương tự với 2 điều kiện để chúng ta tìm ra điểm thi của từng bạn. Vậy làm núm nào để tìm ra? vẫn là hàm VLOOKUP/CHOOSE, từ bây giờ bạn cần ghép 2 điều kiện với nhau bằng dấu và (dấu “and”/”và”), thuộc với việc ghép 2 cột chứa giá trị dò search với nhau cũng với dấu &. Ta gồm 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à phương pháp mảng, đòi hỏi chúng ta phải dấn CTRL+SHIFT+ENTER, thay vì chưng Enter (trả về #NA), bây giờ các các bạn sẽ thấy gồm móc sừng trâu mở ra trong công thức.

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

Ví dụ 3: INDEX/MATCH tìm kiếm kiếm theo rất 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, bắt buộc phải bao gồm nhấn CTRL+SHIFT+ENTER. Bởi sao dịp lại là 1, cơ hội lại TRUE? khi chúng ta chỉ có 1 biểu thức, hôm nay kết quả đang trả về TRUE/FALSE, khi có 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 tất cả một biểu thức: (Biểu thức so sánh) => (=)Giá trị dò search 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 dịp nào? khi chúng ta cần tìm giá trị không thỏa theo biểu thức so sánh của mình.

Dùng INDEX/MATCH phối hợp INDEX để biến hóa công thức mảng thành công xuất sắc thức bình thường.

Xem thêm: Active Win 10 Bản Quyền Vĩnh Viễn, Hướng Dẫn Kích Hoạt Key Bản Quyền Windows 10

Trong lấy ví dụ như 3, các bạn làm quen thuộc với cách làm mảng, yên cầu thao tác bắt buộc nhấn CTRL+SHIFT+ENTER, nhằm tránh câu hỏi phải làm làm việc này, chúng ta cũng có thể kết hòa hợp thêm hàm INDEX bên trong hàm MATCH để trả về giá bán trị đầu tiên trong list MATCH tìm kiếm thấy.

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

Với cú pháp từ ô G7, G4 trong ví dụ bên 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 cái số “, 0), 0)”, số 0 thứ nhất cho hàm INDEX(Biểu thức,0). Số 0 sau cùng cho hàm MATCH(,,0).

Làm report chi tiết, trích lọc hóa 1-1 với INDEX phối kết hợp COUNTIFS. Kết quả trả về nhiều công dụng từ một hoặc nhiều điều kiện.

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

Với quý hiếm dò tra cứu “Nguyễn Thị Đét” bạn có không ít kết trái trả về, vậy bao gồm cách như thế nào liệt kê được vớ cả công dụng không? Câu trả lời là có. Với cú pháp (0=COUNTIFS(<$<Ô thứ nhất trả về kết quả>:<Ô trước tiên 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 điều kiện đã trả về công dụng trước kia hay chưa? Nếu vẫn trả về tác dụng rồi, thì loại bỏ để lấy loại tiếp theo. Từ bây giờ ta vẫn có công dụng mong muốn.

Kết trái trả về #NA là không tìm kiếm thấy nữa, để không hiển thị lỗi, bạn 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, mình cần cố định cả ô $E$2, nhằm khi kéo cách làm xuống sẽ thắt chặt và cố định ô quý giá dò tìm.$F$1:F1, vì sao chỉ cố định và thắt chặt cái đầu tiên, vì chưng để lúc kéo xuống bên dưới, nó sẽ biến đổi $F$1:F<2->n>.

Làm vắt nào nhằm in phiếu xuất kho có nhiều sản phẩm? cùng một phiếu, có không ít mã thành phầm khác nhau

Ví dụ với cùng 1 mã xuất kho, bạn sẽ xuất ra nhiều sản phẩm khác nhau. Hôm nay in phiếu xuất kho, các bạn chỉ việc 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, lúc này COUNTIFS làm nhiệm vụ đánh số thứ tự giúp bọn chúng ta. Vẫn chính là cột dây vào 1 đầu cột, dây sót lại thả tự do để diều cất cánh cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), với $E$9 là quý hiếm dò tìm, cũng phải cố định và thắt chặt để lúc kéo xuống họ không chuyển đổi điều kiện tìm kiếm. Bây giờ các các bạn sẽ thấy số tăng ngày một nhiều theo vùng Mã phiếu xuất kho, nếu không kiếm thấy nữa, thì chỉ là tái diễn cái cuối cùng tìm thấy.

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

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

Để tham khảo thêm các chúng ta cũng có thể sử dụng tính năng tìm kiếm trên web tại ô search kiếm, hoặc tìm kiếm với Google, hãy thêm tự khóa “bdskingland.com” + “từ khóa”. Ví dụ: “bdskingland.com”,”INDEX/MATCH”.