INDEX/MATCH - Tìm kiếm nhiều điều kiện, và trả về nhiều kết quả

Học Excel Online chuồn thâm thúy vô cơ hội dùng phối hợp hàm index và match. Cho chúng ta năng lực dò thám tìm kiếm nhiều ĐK gần giống trả về nhiều kết quả

INDEX/MATCH người sử dụng như vậy nào?

• Cú pháp thông thường gặp gỡ, hoặc người sử dụng với VLOOKUP:

Nếu với VLOOKUP, tao sở hữu công thức như sau:
=VLOOKUP(Giá trị dò thám dò thám, Vùng tài liệu [có: Cột dò thám dò + Cột kết quả], Thứ tự động cột trả về, Tìm chủ yếu xác/gần đúng)

Bạn đang xem: INDEX/MATCH - Tìm kiếm nhiều điều kiện, và trả về nhiều kết quả

Ta sở hữu ví dụ sau, với hàm VLOOKUP chúng ta chú ý những yếu tố sau:

• Giá trị dò thám dò là “Huỳnh Văn Vê“, sở hữu vô cột vùng kể từ A1:A8, và cột chứa chấp cơ luôn luôn nằm bên cạnh trái khoáy ngoài nằm trong vùng tài liệu (A1:C8).
• Vùng dữ liệu: A1:C8, khi chúng ta quét dọn vùng tài liệu luôn luôn trực tiếp ghi nhớ, tất cả chúng ta tiếp tục nên cố định và thắt chặt vì thế phím F4, trước lúc thực hiện ngẫu nhiên việc gì tiếp theo: $A$1:$C$8. Vì sao? Vì khi tất cả chúng ta kéo công thức, vùng tham ô chiếu tài liệu tiếp tục luôn luôn được cố định và thắt chặt.
• Cột trả về, tao điểm bám theo trật tự kể từ trái khoáy sang trọng nên, tính kể từ cột chứa chấp độ quý hiếm dò thám dò thám. Tại ví dụ bên dưới là cột loại 3.
• Luôn luôn luôn ghi nhớ, nhập số 0 sau cuối ở hàm VLOOKUP và hàm MATCH. Tại sao? Vì số 0 ứng với FALSE, là dò thám tìm kiếm đúng chuẩn. Luôn luôn luôn dò thám tìm kiếm đúng chuẩn. Tại sao không tìm kiếm tương đối? Có một vài tình huống tất cả chúng ta tiếp tục dò thám kha khá, sấp xỉ, tuy nhiên này đó là vài ba tình huống khi chúng ta đang được nắm vững.

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

Và thời điểm hiện nay tất cả chúng ta sở hữu công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Với E2 là độ quý hiếm cần thiết dò thám, vô vùng tài liệu kể từ A1:C8, và lốt $ đem ý nghĩa sâu sắc cố định và thắt chặt vùng tài liệu dò thám tìm kiếm nhằm khi kéo công thức vùng tiếp tục cố định và thắt chặt. Cột tài liệu trả về là cột loại 3, tính từ vựng trí điểm kể từ cột chứa chấp độ quý hiếm dò thám dò sang trọng phía bên phải. Và số 0, là dò thám đúng chuẩn, luôn luôn luôn luôn là số 0.

Vậy với INDEX thì tiếp tục thay cho thay đổi như vậy nào:
=INDEX(Cột thành phẩm, MATCH(Giá trị dò thám dò thám, Cột dò thám dò chứa chấp độ quý hiếm cần thiết dò thám, Tìm chủ yếu xác/gần đúng)

Lúc này những các bạn sẽ thấy hàm sở hữu cú pháp như sau: =INDEX(Vùng thành phẩm, Dòng, Cột).

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

Vì sao lại phát biểu người sử dụng INDEX/MATCH dễ dàng rộng lớn VLOOKUP?

VLOOKUP yên cầu cột chứa chấp độ quý hiếm dò thám dò nên ở ngoài nằm trong phía bên trái vùng tài liệu. Nếu nằm bên cạnh nên thì thời điểm hiện nay nên người sử dụng hàm mảng kết phù hợp với hàm CHOOSE nhằm lấy thành phẩm ứng. Vậy nằm trong xem xét lại ví dụ, chúng ta chỉ việc quét dọn vùng lựa chọn cột thành phẩm, dò thám vô cột chứa chấp độ quý hiếm dò thám dò thám. Thế là xong!

Ngược lại với ví dụ trước cơ, tất cả chúng ta sở hữu cột Lớp ở ngoài nằm trong phía bên trái, và việc là kể từ thương hiệu Học viên, tất cả chúng ta tiếp tục dò thám rời khỏi lớp của Học viên cơ. Quý khách hàng tiếp tục viết lách hàm VLOOKUP bám theo như thường thì làm sao? Nghĩ coi nhé? Vậy với hàm VLOOKUP, chúng ta nên người sử dụng phối hợp hàm CHOOSE, với cú pháp =CHOOSE({1,2}, Cột chứa chấp độ quý hiếm dò thám dò thám, Cột Kết quả).

Vậy tao sở hữu cú pháp tổng quát tháo như sau: =VLOOKUP(Giá trị dò thám dò thám, CHOOSE({1,2}, Cột chứa chấp độ quý hiếm dò thám dò thám, Cột kết quả), Cột trả về[2], Tìm đúng chuẩn [0])

Nếu lốt phân cơ hội của khách hàng là dấu chấm phẩy, thì công thức tiếp tục là: =VLOOKUP(Giá trị dò thám tìm; CHOOSE({1 \ 2}; Cột chứa chấp độ quý hiếm dò thám tìm; Cột kết quả); Cột trả về[2]; Tìm đúng chuẩn [0])

Với INDEX/MATCH thì chúng ta thấy vẫn như ví dụ 1, =INDEX(Vùng thành phẩm, MATCH(Giá trị dò thám dò thám, Vùng dò thám dò thám, Tìm chủ yếu xác). Đơn giản rồi nên ko nào?

Xem thêm: Công thức tìm tọa độ giao điểm của hai đường thẳng hay, chi tiết | Toán lớp 9.

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

Ta sở hữu ví như bên trên, khi này còn có 2 chúng ta “Nguyễn Thị Đét” nằm trong thương hiệu học tập 2 lớp không giống nhau, tương tự với 2 ĐK nhằm tất cả chúng ta dò thám rời khỏi điểm thi đua của từng chúng ta. Vậy thực hiện thế này nhằm dò thám ra? Vẫn là hàm VLOOKUP/CHOOSE, thời điểm hiện nay bạn phải ghép 2 ĐK cùng nhau vì thế lốt & (dấu “and”/”và”), cùng theo với việc ghép 2 cột chứa chấp độ quý hiếm dò thám dò cùng nhau cũng tương tự lốt &. Ta sở hữu cú pháp như sau:

=VLOOKUP([Giá trị dò thám A]&[Giá trị dò thám dò B]&[Giá trị dò thám dò n], CHOOSE({1, 2}, [Vùng cột chứa chấp độ quý hiếm A]&[Vùng cột chứa chấp độ quý hiếm B]&[Vùng cột chứa chấp độ quý hiếm n], [Vùng cột kết quả]), 2 là Cột trả về, 0 là Tìm chủ yếu xác)

Và đây là công thức mảng, yên cầu chúng ta nên nhấn CTRL+SHIFT+ENTER, thay cho Enter (trả về #NA), thời điểm hiện nay những các bạn sẽ thấy sở hữu móc sừng trâu xuất hiện nay vô công thức.

Tại sao lại MATCH khi dò thám 1, khi lại là TRUE và khi này nên nhấn CTRL+SHIFT+ENTER?

Với INDEX/MATCH, chúng ta sở hữu cú pháp như sau:

=INDEX(Vùng thành phẩm, MATCH(1,([Giá trị dò thám dò A]=[Vùng cột độ quý hiếm A])*([Giá trị dò thám dò B]=[Vùng cột độ quý hiếm B]*([Giá trị dò thám dò n]=[Vùng cột độ quý hiếm n]),0)

Và đó là công thức mảng, nên nên sở hữu nhấn CTRL+SHIFT+ENTER. Vì sao khi lại là một, khi lại TRUE? Khi chúng ta chỉ có một biểu thức, thời điểm hiện nay thành phẩm tiếp tục trả về TRUE/FALSE, khi sở hữu 2 biểu thức TRUE*TRUE, Excel tiếp tục trả TRUE trở thành 1*1 = 1.

• Giá trị dò thám dò là TRUE, khi sở hữu một biểu thức: (Biểu thức ví sánh) => ([Giá trị A]=[Vùng cột chứa chấp độ quý hiếm A])
• Giá trị dò thám dò là một, khi sở hữu nhị biểu thức trở lên: (Biểu thức 1)*(Biểu thức 2) => ([Giá trị A]=[Vùng cột chứa chấp độ quý hiếm A])*([Giá trị B]=[Vùng cột chứa chấp độ quý hiếm B])*([Giá trị n]=[Vùng cột chứa chấp độ quý hiếm n])
• Tìm FALSE khi nào? Khi bạn phải dò thám độ quý hiếm ko thỏa bám theo biểu thức đối chiếu của tớ.

Dùng INDEX/MATCH phối hợp INDEX nhằm quy đổi công thức mảng thành công xuất sắc thức thông thường.

Trong ví dụ 3, chúng ta thích nghi với công thức mảng, yên cầu thao tác nên nhấn CTRL+SHIFT+ENTER, nhằm tách việc nên thực hiện thao tác này, chúng ta có thể phối hợp thêm thắt hàm INDEX bên phía trong hàm MATCH nhằm trả về độ quý hiếm thứ nhất vô list MATCH nhìn thấy.

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

• =INDEX(Vùng thành phẩm, MATCH(TRUE, INDEX(Biểu thức, 0), 0))
• =INDEX(Vùng thành phẩm, MATCH(1, INDEX((Biểu thức 1)*(Biểu thức 2), 0), 0))
• Lưu ý, luôn luôn sở hữu 2 loại số “, 0), 0)”, số 0 thứ nhất mang đến hàm INDEX(Biểu thức,0). Số 0 sau cuối mang đến hàm MATCH(,,0).

Làm report cụ thể, trích thanh lọc hóa đơn với INDEX phối hợp COUNTIFS. Kết trái khoáy trả về nhiều thành phẩm từ là một hoặc nhiều ĐK.

Với độ quý hiếm dò thám dò “Nguyễn Thị Đét” chúng ta có tương đối nhiều SERP, vậy sở hữu cơ hội này liệt kê được toàn bộ thành phẩm không? Câu vấn đáp là sở hữu. Với cú pháp (0=COUNTIFS([$[Ô thứ nhất trả về kết quả]:[Ô thứ nhất trả về kết quả]], Vùng kết quả), vô ví dụ: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Xét thêm thắt ĐK đang được trả về thành phẩm trước cơ hoặc chưa? Nếu đang được trả về thành phẩm rồi, thì loại trừ nhằm lấy loại tiếp theo sau. Lúc này tao sẽ sở hữu thành phẩm mong ước.

• Kết trái khoáy trả về #NA là không tìm kiếm thấy nữa, nhằm ko hiển thị lỗi, chúng ta có thể người sử dụng hàm IFERROR(Công thức, “”).
• Biểu thức ĐK bám theo thương hiệu thứ nhất, bản thân cần thiết cố định và thắt chặt cả dù $E$2, nhằm khi kéo công thức xuống tiếp tục cố định và thắt chặt dù độ quý hiếm dò thám dò thám.
• $F$1:F1, vì thế sao chỉ cố định và thắt chặt loại thứ nhất, vì thế nhằm khi kéo xuống bên dưới, nó sẽ bị phát triển thành $F$1:F[2->n].

Làm thế này nhằm in phiếu xuất kho có tương đối nhiều sản phẩm? Cùng một phiếu, có tương đối nhiều Model không giống nhau

Ví dụ với cùng một mã xuất kho, các bạn sẽ xuất rời khỏi nhiều thành phầm không giống nhau. Lúc này in phiếu xuất kho, chúng ta chỉ việc nhập mã phiếu xuất kho, tiếp tục trả về list thành phầm ứng.

Ta lập cột phụ tham ô chiếu bám theo mã phiếu xuất kho, thời điểm hiện nay COUNTIFS thực hiện trách nhiệm khắc số trật tự hùn tất cả chúng ta. Vẫn là cột chão vô 1 đầu cột, chão sót lại thả tự tại nhằm diều cất cánh cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), và $E$9 là độ quý hiếm dò thám dò thám, cũng nên cố định và thắt chặt nhằm khi kéo xuống tất cả chúng ta bất biến ĐK dò thám dò thám. Lúc này những các bạn sẽ thấy số tăng dần dần bám theo vùng Mã phiếu xuất kho, còn nếu như không nhìn thấy nữa, thì đơn thuần tái diễn loại sau cuối nhìn thấy.

Xem thêm: Cách viết địa chỉ bằng tiếng Anh dễ nhất | ZIM Academy

Lúc này về bên Sheet Phiếu Xuất Kho nhằm in dán, chỉ việc lập công thức ứng như sau, bản thân phân tích và lý giải kể từ trái khoáy sang:

• STT: Nếu Sản phẩm trả về trống rỗng, thì tiếp tục trả về trống rỗng, ngược lại lấy độ quý hiếm dù ngay lập tức bên trên + 1. Hàm N() ở trên đây nhằm đánh giá trong trường hợp là chữ tiếp tục trả về 0, trong trường hợp là số thì trả về số ứng. N(“STT”) => 0+1 = 1, nếu như thành phầm sở hữu thành phẩm. Tìm hiểu thêm thắt về Hướng dẫn dùng hàm N vô Excel.
• Sản phẩm: =INDEX(Vùng thành phẩm [ nhiều cột], MATCH(ROW(1:1), Vùng cột phụ,0), Cột trả về). Ta sở hữu Vùng thành phẩm là B1:D6, thời điểm hiện nay chúng ta có thể áp dụng chỉ cột B1:B6 cũng khá được, tuy nhiên tất cả chúng ta hoàn toàn có thể trả về cột ứng tao mong ước. Tại trên đây tao sở hữu 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 tất cả chúng ta kéo công thức xuống B13, nó phát triển thành ROW(2:2) = 2.
• Số lượng: Tại trên đây chúng ta thấy chỉ không giống từng Cột trả về đích không? Tại sao lại là COLUMN(B1)? Hàm COLUMN(B1) tiếp tục trả về thành phẩm cột B1 là cột từng nào, tức là 2. Khi kéo sang trọng nên, nó phát triển thành COLUMN(C1), tức là 3. Vậy khi kéo sang trọng trái khoáy địa điểm cột Sản phẩm thì nó trở thành gì? Quý khách hàng đoán xem? Là COLUMN(A1), tức là một. Giờ thì chúng ta hiểu vì thế sao bản thân nhằm công thức mang đến chúng ta thấy rồi nên không?
• Chỗ #NA của STT và Sản phẩm, bản thân cố ý nhằm thành phẩm vì vậy, nếu như mình muốn ko hiển thị #NA, hãy người sử dụng IFERROR bám theo cột Số lượng và Kho nhé!

Để dò thám hiểu thêm thắt những chúng ta có thể dùng tác dụng dò thám tìm kiếm bên trên trang web bên trên dù dò thám dò thám, hoặc dò thám tìm kiếm với Google, hãy thêm thắt kể từ khóa “khoayduoc.edu.vn” + “từ khóa”. Ví dụ: “khoayduoc.edu.vn”,”INDEX/MATCH”.

Đón xem: – Bí kíp võ lâm – Tập 2: SUMPRODUCT thần chưởng.