Dò tìm kiếm hàng cùng cột trong excel là công việc thường xuyên của chúng ta kế toán hay nhân sự. VLOOKUP, HLOOKUP là 2 hàm dò tìm dữ liệu và thông dụng với nhiều người dân nhưng có số lượng giới hạn là quý hiếm trả về phải nằm tại cột bên nên so với giá trị dò tìm với hàm VLOOKUP và nằm tại vị trí cột bên dưới với hàm HLOOKUP.
Sự phối kết hợp của hàm INDEX kết hợp với hàm MATCH sẽ mang về sự linh hoạt rộng trong vấn đề dò tra cứu so với những hàm không giống trong Excel. Nội dung bài viết sẽ hướng dẫn chúng ta cách sử dụng 2 hàm này phối kết hợp để sự dụng trong việc dò tìm kiếm hàng với cột vào Excel.
Bạn đang xem: Các hàm dò tìm trong excel
Trong ví dụ sinh hoạt dưới, ta tất cả bảng 1 liệt kê hà nội của những nước Đông phái mạnh Á và đề xuất điền dữ liệu khớp ứng ở bảng 2. Trường vừa lòng này ta không thể sử dụng hàm VLOOKUP nhưng hàm INDEX kết hợp hàm MATCH sẽ xử lý được điều này.
Dò search hàng với cột vào Excel bằng phương pháp kết đúng theo hàm INDEX và hàm MATCH
Contents
1. Cấu tạo hàm INDEX
Hàm INDEX trả về quý giá theo vị trí của hàng với cột trong một bảng hoặc một phạm vi.
Cú pháp hàm: =INDEX(array;row_num;column_num)
Trong đó:
array: vùng ô hoặc một mặt hàng số mảng nào đó;row_num: lựa chọn hàng vào mảng từ kia trả về một giá chỉ trị;column: lựa chọn cột trong mảng từ đó trả về một giá trị.2. Cấu trúc hàm MATCH
Hàm MATCH trả về vị trí kha khá của một giá trị trong một phạm vi.
Cú pháp hàm: =MATCH(lookup_value;lookup_array;match_type)
Trong đó:
Lookup_value: giá trị tìm kiếm trong mảng Lookup_array.Lookup_array: mảng xuất xắc phạm vị ô được kiếm tìm kiếm.Match_type: dạng hình tìm kiếm.3. Cách sử dụng hàm MATCH để dò tìm mặt hàng trong Excel
Ở lấy một ví dụ trên mong muốn tìm tổ quốc Việt Nam làm việc hàng vật dụng mấy trong bảng 1 ta triển khai như sau:
Chúng ta nhập cách làm ô G3 =MATCH(F3;$B$2:$B$13;0) và ấn Enter công dụng trả về là hàng đồ vật 5 trong bảng 01.
4. Cách sử dụng hàm MATCH để dò tìm kiếm cột vào Excel
Ở lấy một ví dụ trên ý muốn tìm thành phố hà nội ở cột thiết bị mấy vào bảng 1 ta tiến hành như sau:
Chúng ta nhập cách làm ô G3 =MATCH($G$2;$A$2:$C$2;0) và ấn Enter hiệu quả trả về là cột sản phẩm công nghệ 3 trong bảng 01.
5. Phối hợp hàm INDEX với hàm MATCH dò search trong Excel
Quay quay lại với nhà đề chính của bài viết này là sử dụng phối hợp hàm INDEX với MATCH dò tra cứu hàng với cột trong Excel vào ví du bên trên ta triển khai như sau:
Bước 1: bọn họ nhập cách làm ô G13=INDEX($A$2:$C$13;MATCH(F4;$B$2:$B$13;0);MATCH($G$2;$A$2:$C$2;0)).
Bước 2: Ấn Enter và ta được hiệu quả thu đô vn là Hà Nội.
Bước 3: Copy công thức vào những ô tiếp sau ta đã thu được hiệu quả .
Dò tìm hàng với cột vào Excel bằng cách kết đúng theo hàm INDEX và hàm MATCH
Như vậy, bài viết trên shop chúng tôi đã phía dẫn chúng ta cách sử dụng phối hợp hàm INDEX cùng hàm MATCH để dò search hàng với cột vào Excel . Hy vọng bài viết sẽ bổ ích với chúng ta trong quá trình làm việc. Chúc chúng ta thành công!
Học Excel Online đi sâu vào giải pháp sử dụng phối hợp hàm index cùng match. Cho mình khả năng kiếm tìm kiếm những đ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 gồm công thức như sau:=VLOOKUP(Giá trị dò tìm, Vùng tài liệuTa gồm ví dụ sau, cùng với hàm VLOOKUP các bạn lưu ý những vấn đề sau:
Và bây giờ chúng ta tất cả công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Cùng với E2 là giá bán trị đề xuất tìm, vào vùng dữ liệu từ A1:C8, cùng dấu $ mang ý nghĩa sâu sắc cố định vùng dữ liệu tìm kiếm nhằm khi kéo công thức vùng sẽ rứa định. Cột tài liệu trả về là cột thiết bị 3, tính từ vị trí đếm tự cột đựng giá trị dò search sang mặt phải. Và số 0, là tìm bao gồm xác, luôn luôn là số 0.
Vậy cùng với INDEX thì sẽ chuyển đổi như thay nào:=INDEX(Cột kết quả, MATCH(Giá trị dò tìm, Cột dò tìm đựng giá trị bắt buộc tìm, Tìm thiết yếu xác/gần đúng)
Dù đã tất cả phần mềm, nhưng khả năng Excel vẫn cực kì quan trọng cùng với kế toán, bạn đã vững vàng Excel chưa? Hãy để tôi góp bạn, đăng ký khoá học Excel:
Lúc này các các bạn sẽ thấy hàm 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ông giống 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 do cả cột cất giá trị dò tìm.Hàm MATCH(Giá trị dò tìm, Vùng tìm kiếm, Tìm chủ yếu xác/tương đối). Hôm nay E3 là quý giá dò tìm, và vùng tìm kiếm chỉ với đúng vùng cột đựng giá trị dò tìm: A1:A8, tựa như VLOOKUP, vùng search kiếm luôn luôn phải thắt chặt và cố định vùng $A$1:$A$8. Và luôn luôn luôn tìm bao gồm xác, là số 0 hoặc FALSE.Với phương pháp trên, ta thấy hàm MATCH vẫn trả về cực hiếm là 2, tương ứng dòng tìm kiếm thấy từ bên trên xuống. INDEX($C$1:$C$8, 2) => kết quả là 7.Vì sao lại nói dùng INDEX/MATCH dễ dàng hơn VLOOKUP?
VLOOKUP đòi hỏi cột chứa giá trị dò tìm buộc phải nằm xung quanh cùng phía trái vùng dữ liệu. Nếu nằm cạnh sát phải thì hôm nay phải sử dụng hàm mảng kết phù hợp với hàm CHOOSE để lấy công dụng tương ứng. Vậy thuộc xem lại ví dụ, chúng ta chỉ việc quét vùng lựa chọn cột kết quả, tìm trong cột đựng giá trị dò tìm. Thế là xong!
Ngược lại với ví dụ như trước đó, họ có cột Lớp nằm ngoài cùng mặt trái, và việc là từ bỏ tên học tập viên, chúng ta sẽ đưa ra lớp của học tập viên đó. Bạn sẽ viết hàm VLOOKUP theo như thông thường thế nào? nghĩ xem nhé? Vậy cùng với hàm VLOOKUP, các bạn phải dùng phối hợp hàm CHOOSE, cùng với cú pháp =CHOOSE(1,2, Cột đựng 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 chuẩn <0>)
Nếu dấu phân cách của công ty 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 đắn <0>)
Với INDEX/MATCH thì chúng ta 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 chủ yếu xác). Đơn giản rồi yêu cầu không nào?
Dùng VLOOKUP/CHOOSE hoặc INDEX/MATCH tìm theo rất nhiều điều kiện
Ta có ví dụ như trên, từ bây giờ có 2 chúng ta “Nguyễn Thị Đét” cùng tên học 2 lớp không giống nhau, tương tự với 2 đk để bọn họ tìm ra điểm thi của từng bạn. Vậy làm cố gắng nào để tìm ra? vẫn là hàm VLOOKUP/CHOOSE, bây giờ bạn yêu cầu ghép 2 điều kiện với nhau bằng dấu và (dấu “and”/”và”), cùng với việc ghép 2 cột cất giá trị dò kiếm tìm với nhau cũng như với dấu &. Ta tất cả cú pháp như sau:
=VLOOKUP(
Và đây là bí quyết mảng, đòi hỏi chúng ta phải thừa nhận CTRL+SHIFT+ENTER, thay vì chưng Enter (trả về #NA), từ bây giờ các bạn sẽ thấy 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 cùng lúc nào phải nhấn CTRL+SHIFT+ENTER?
Ví dụ 3: INDEX/MATCH tìm kiếm theo không ít điều kiệnVới INDEX/MATCH, các bạn có cú pháp như sau:
=INDEX(Vùng kết quả, MATCH(1,(
Và đây là công thức mảng, cần phải gồm nhấn CTRL+SHIFT+ENTER. Do sao thời gian lại là 1, dịp lại TRUE? khi bạn chỉ có một biểu thức, từ bây giờ kết quả sẽ trả về TRUE/FALSE, khi bao gồm 2 biểu thức TRUE*TRUE, Excel sẽ gửi TRUE thành 1*1 = 1.
Giá trị dò search là TRUE, khi gồm một biểu thức: (Biểu thức so sá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.
Trong ví dụ 3, các bạn làm quen thuộc với phương pháp mảng, đòi hỏi thao tác yêu cầu nhấn CTRL+SHIFT+ENTER, để tránh câu hỏi phải làm thao tác này, chúng ta có thể kết đúng theo thêm hàm INDEX bên phía trong hàm MATCH nhằm trả về giá chỉ trị trước tiên trong danh sách MATCH tìm kiếm thấy.
Ví dụ 4: INDEX/MATCH dò tìm theo rất nhiều điều kiệnVớ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 mẫu số “, 0), 0)”, số 0 trước tiên 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 thanh lọc hóa solo với INDEX kết hợp COUNTIFS. Hiệu quả trả về nhiều tác dụng từ một hoặc nhiều điều kiện.
Với giá trị dò tra cứu “Nguyễn Thị Đét” bạn có khá nhiều kết quả trả về, vậy tất cả cách nào liệt kê được vớ cả tác dụng không? Câu trả lời là có. Cùng với cú pháp (0=COUNTIFS(<$<Ô đầu tiên trả về kết quả>:<Ô trước tiên trả về kết quả>>, Vùng kết quả), vào ví dụ: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Xét thêm điều kiện đã trả về tác dụng trước kia hay chưa? Nếu vẫn trả về công dụng rồi, thì đào thải để lấy mẫu tiếp theo. Lúc này ta đang có kết quả mong muốn.
Kết trái trả về #NA là không kiếm thấy nữa, để không hiển thị lỗi, bạn có thể dùng hàm IFERROR(Công thức, “”).Biểu thức điều kiện theo tên đầu tiên, bản thân cần thắt chặt và cố định cả ô $E$2, để khi kéo bí quyết xuống sẽ cố định ô quý hiếm dò tìm.$F$1:F1, vì chưng sao chỉ thắt chặt và cố định cái đầu tiên, vày để khi kéo xuống bên dưới, nó sẽ biến đổi $F$1:F<2->n>.Làm thay nào để in phiếu xuất kho có không ít sản phẩm? cùng một phiếu, có tương đối nhiều mã sản phẩm khác nhau
Ví dụ với một mã xuất kho, các bạn sẽ xuất ra nhiều sản phẩm khác nhau. Lúc này in phiếu xuất kho, các bạn chỉ vấn đề nhập mã phiếu xuất kho, sẽ 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, bây giờ COUNTIFS làm trách nhiệm đánh số trang bị tự giúp chúng ta. Vẫn chính là cột dây vào 1 đầu cột, dây còn sót lại thả tự do để diều bay cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), với $E$9 là quý giá dò tìm, cũng phải cố định và thắt chặt để lúc kéo xuống chúng ta không biến đổi điều kiện search kiếm. Từ bây giờ các các bạn sẽ thấy số tăng dần đều 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 ở đầu cuối tìm thấy.
Xem thêm: Hột le là gì? vị trí hột le là gì, 4 bước kích thích hột le giúp nàng lên đỉnh
Lúc này quay trở lại Sheet Phiếu Xuất Kho nhằm in ấn, chỉ bài toán lập công thức khớp ứng như sau, mình phân tích và lý giải từ trái sang:
Sản phẩm: =INDEX(Vùng hiệu quả < nhiều cột>, MATCH(ROW(1:1), Vùng cột phụ,0), Cột trả về). Ta có Vùng kết quả là B1:D6, dịp này bạn cũng 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 tương ứng ta ý muốn muốn. Ở đây ta gồm vùng B1:D6, cột trả về là 1 trong = cột B. Còn ROW(1:1) là gì? ROW(1:1) trả về 1. Cùng khi họ kéo bí quyết xuống B13, nó vươn lên là ROW(2:2) = 2.Số lượng: Ở đây bạn thấy chỉ khác mỗi Cột trả về đúng không? nguyên nhân lại là COLUMN(B1)? Hàm COLUMN(B1) vẫn trả về kết quả cột B1 là cột bao nhiêu, có nghĩa là 2. Lúc kéo sang trọng phải, nó trở thành COLUMN(C1), có nghĩa là 3. Vậy khi kéo sang trái nơi cột thành phầm thì nó thành gì? bạn đoán xem? Là COLUMN(A1), có nghĩa là 1. Giờ đồng hồ thì bạn hiểu vị sao bản thân để phương pháp cho các bạn thấy rồi đề xuất không?Chỗ #NA của STT và Sản phẩm, mình cố tình để tác dụng 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 bạn có thể sử dụng tính năng tìm tìm trên web tại ô tra cứu kiếm, hoặc search kiếm cùng với Google, hãy thêm từ khóa “shthcm.edu.vn” + “từ khóa”. Ví dụ: “shthcm.edu.vn”,”INDEX/MATCH”.