Khi tìm kiếm tài liệu trên trang tính Excel, bạn thường áp dụng hàm nào? chắc rằng câu trả lời phổ cập nhất là hàm VLOOKUP. Vậy các bạn đã biết đến những hàm tra cứu kiếm vào Excel không giống chưa? Trong nội dung bài viết ngày hôm nay, chúng ta hãy cùng edutainment.edu.vn tìm hiểu về hàm MATCH và các ứng dụng mang tính đỉnh cao của hàm lúc kết hợp với các hàm Excel nhé.
Bạn đang xem: Cách dùng hàm match trong excel
Giới thiệu hàm MATCH trong Excel
Hàm MATCH trong Excel là hàm tham chiếu có tác dụng tìm kiếm một giá trị được khẳng định trong một phạm vi trang tính với trả về vị trí tương đối của giá trị đó. Điểm đặc biệt quan trọng khiếnhàm MATCHtrở đề xuất nổi bật đó là hiệu quả về tối ưu khi thao tác với những bảng, những mảng số liệu phức tạp. Hãy ban đầu khám phá hàm kiếm tìm kiếm vào Excel này ngay thôi!
Cú pháp hàm MATCH vào Excel
Với công dụng hàm lấy giá trị của ô vào Excel, hàm MATCH trong Excel có cú pháp như sau:
=MATCH(lookup_value,lookup_array,
Trong đó:
lookup_value: Là giá chỉ trị phải tìm kiếm trong mảng. Giá trị này có thể là giá trị văn bản, quý hiếm số, giá trị súc tích hoặc một quý hiếm tham chiếu ô.lookup_array: Là mảng, giỏi phạm vi tìm kiếm kiếm quý hiếm lookup_value.match_type: Là đẳng cấp tìm kiếm. Chúng ta cũng có thể chọn 1 trong các 3 mẫu mã tìm kiếm bên dưới đây, hoặc bỏ lỡ tham số này.match_type = 1 (hoặc bỏ qua): Hàm MATCH search kiếm giá bán trị lớn nhất trong mảng lookup_array đáp ứng điều kiện bé dại hơn hoặc bằng giá trị lookup_value. Với loại tìm kiếm này, bọn họ phải sắp xếp dữ liệu theo đồ vật tự tăng dần, A-Z, FALSE và TRUE.match_type = 0: Hàm MATCH kiếm tìm kiếm giá bán trị trước tiên xuất hiện tại trong mảng lookup_array đáp ứng nhu cầu điều kiện bởi giá trị lookup_value. Với hình dạng tìm tìm này, họ không rất cần phải sắp xếp dữ liệu theo một vật dụng tự nên nào.match_type = -1: Hàm MATCH kiếm tìm kiếm giá trị nhỏ tuổi nhất vào mảng lookup_array đáp ứng nhu cầu điều kiện to hơn hoặc bằng giá trị lookup_value. Với vẻ bên ngoài tìm tìm này, họ phải thu xếp dữ liệu theo sản phẩm công nghệ tự giảm dần, Z-A, TRUE cùng FALSE.Một số xem xét với hàm MATCH trong Excel
Khi thực hiện hàm tham chiếu MATCH trong Excel, chúng ta hãy chú ý một số điểm lưu ý sau phía trên của hàm:
Hàm MATCH là hàm lấy quý giá của ô trong Excel, tức là trả về vị trí kha khá chứa quý giá lookup_value, chứ không cần trả về chủ yếu giá trị đó.Hàm MATCH không phân minh chữ hoa cùng chữ thường.Hàm MATCH có thể tìm kiếm những giá trị văn bạn dạng chứa cực hiếm đặc biệt: ký kết tự hoa thị "*" xác định look_value là 1 chuỗi ký kết tự, cam kết tự hỏi chấm "?" xác định lookup_value là cam kết tự đơn. Trong trường đúng theo lookup_value của người sử dụng là cam kết tự "?", bạn sẽ cần sử dụng ký từ dấu bửa "~" trước ký tự "?".Nếu vào phạm vi lookup_array đựng nhiều hơn một quý hiếm khớp cùng với lookup_value, hàm MATCH vào Excel luôn trả về cực hiếm đầu tiên.Nếu vào phạm vi lookup_array không đựng giá trị lookup_value, hàm search kiếm trong Excel trả về lỗi #N/A.Trong trường hòa hợp hàm tham chiếu MATCH trả về quý giá lỗi #N/A, gồm thể các bạn sẽ cần lưu lại lại các ô đó để tránh dẫn đến những lỗi khác khi vô tình sử dụng công thức mang lại ô xuất hiện lỗi. Hãy tham khảo cách áp dụng hàm NA để làm được vấn đề này nhé.
Ví dụ cơ phiên bản về cách thực hiện hàm MATCH
Chỉ nói lý thuyết chắc chắn sẽ là không được để bạn hoàn toàn nắm được cách sử dụng hàm MATCH vào Excel. Vậy thì tại phần này, hãy cùng bọn chúng mình tìm hiểu một ví dụ đơn giản dễ dàng dưới đây để triển khai rõ phương thức hoạt động của hàm lấy cực hiếm của ô trong Excel nhé.
Giả sử chúng ta có một bảng điểm tổng kết môn được bố trí theo sản phẩm công nghệ tự bớt dần như sau:
Với bảng dữ liệu trên, nhiệm vụ của họ là xác định vị trí của một học sinh bất kỳ trong bảng. Hãy rước ví dụ học sinh tên Nhung. Làm cầm cố nào để biết lấy điểm của học sinh Nhung sẽ xếp vị trí thứ mấy vào bảng điểm tổng hợp?
Để trả lời câu hỏi trên và tìm vị trí xếp hạng của học viên Nhung, bọn họ sẽ bắt buộc đến cách sử dụng hàm MATCH đến ô E3 như sau:
=MATCH(E2,A2:A11,0)
Trong đó:
lookup_value = E2: quý hiếm tham chiếu đến ô chứa tên học sinh cần tìm.lookup_array = A2:A11: Cột học viên (cột A) chứa tên tất cả học sinh trong bảng.match_type = 0: tra cứu kiếm quý giá trong phạm vi lookup_array khớp hoàn toàn với quý giá lookup_value. Với phong cách tìm kiếm này, chúng ta không cần phải sắp xếp đồ vật tự dữ liệu trong bảng.
Như vậy, cách làm hàm MATCH trong Excel sẽ cho chúng ta biết học sinh Nhung xếp vị trí thứ 5 trong cột học sinh. Đó là hào kiệt cơ bản của hàm MATCH. Vậy thì hãy cùng tìm hiểu một áp dụng khác của hàm tra cứu kiếm vào Excel có tên MATCH trong nội dung bài viết dưới phía trên nhé.
Cách về tối ưu hóa công thức hàm MATCH
Hàm MATCH với những ký từ bỏ đại diện
Như đã đề cập ở trên, hàm MATCH có khả năng hiểu những ký tự đại diện như lốt hoa thị "*" cố cho một chuỗi cam kết tự, lốt hỏi chấm "?" rứa cho một ký kết tự đơn. Phương pháp này đặc trưng hữu ích trong những trường vừa lòng tìm tìm mà chúng ta chỉ nhớ được 1 phần trong chuỗi văn bạn dạng cần tìm.
Lưu ý: Khi áp dụng ký tự quan trọng đặc biệt trong công thức hàm MATCH, bắt buộc phải nhập match_type là 0.
Nếu bạn còn mơ hồ về cách sử dụng hàm MATCH với những ký tự đại diện, hãy xem một ví dụ sau đây nhé. Mang sử bạn phải tìm kiếm vị trí của một học sinh nhất định dựa vào bảng điểm mà lại lại quan yếu nhớ đúng mực tên học viên này. Thay bởi đó, bạn chỉ ghi nhớ tên học tập sinh bước đầu bằng những ký từ "Nh". Để giải quyết và xử lý trường vừa lòng này, các bạn sẽ phải sử dụng hàm MATCH với công thức như sau:
=MATCH("Nh*",A2:A11,0)
Nếu bạn đã nhập quý hiếm "Nh*" vào ô E2, chúng ta không cần nhập lại quý giá này vào phương pháp hàm MATCH vào Excel nữa. Cố gắng vào đó, chúng ta cũng có thể đặt thông số lookup_value trong bí quyết hàm MATCH là tham chiếu cho ô E2 như sau:
=MATCH(E2,A2:A11,0)
Bên cạnh đó, chúng ta có thể nhập cực hiếm "Nh" vào ô E2 và sử dụng ký từ bỏ "&" trong công thức hàm MATCH để phối hợp hai điều kiện cho tham số lookup_value:
=MATCH(E2&"*",A2:A11,0)
Lúc này, hàm MATCH trả về tác dụng vị trí số 5 khớp ứng với tên học sinh Nhung. Không quan trọng đặc biệt rằng chúng ta nhập trực tiếp quý hiếm "Nh*" vào phương pháp hàm MATCH vào Excel giỏi nhập giá chỉ trị vào một trong những ô khác với dẫn tham chiếu ô vào cách làm hàm, kết quả nhận được sẽ vẫn luôn là số 5.
Trong một trường đúng theo khác, chúng ta quên mất một cam kết tự nhất mực trong giá trị đề nghị tìm. Hãy học tập cách sử dụng hàm MATCH với lốt hỏi chấm "?" để thay mặt cho ký kết tự các bạn đã quên. Trả sử chúng ta có một công thức như sau:
=MATCH("Nh?ng",A2:A11,0)
Nếu bạn đã nhập vào ô E2 giá trị "Nh?ng", các bạn hoàn toàn có thể tham chiếu tới ô kia trong thông số lookup_value:
=MATCH(E2,A2:11,0)
Như các bạn đã thấy, mặc dầu bạn thiết yếu ghi nhớ đúng đắn giá trị cần tìm kiếm, hàm MATCH vào Excel vẫn có thể tham chiếu bảng dữ liệu và trả về đúng chuẩn vị trí tương đối của một cực hiếm khớp với phần nhiều gì chúng ta đã nhập vào thông số lookup_value. Hàm tra cứu kiếm trong Excel này quả thực lợi hại đúng không ạ nào?
Hàm MATCH khác nhau chữ hoa cùng chữ thường
Excel vẫn mặc định hàm MATCH không có chức năng phân biệt chữ hoa và chữ thường. Vậy trong trường hợp bạn cần sử dụng hàm search kiếm trong Excel cùng với bảng số liệu yêu cầu biệt lập 2 hình trạng chữ thì buộc phải làm nắm nào? Hãy nhằm edutainment.edu.vn chỉ cho các bạn cách xử trí nhé.
Để tạo một phương pháp hàm MATCH riêng biệt được chữ hoa cùng chữ thường, bọn họ sẽ cần phối kết hợp thêm hàm EXACT. Công thức bao quát của bọn họ như sau:
=MATCH(TRUE,EXACT(lookup_array,lookup_value),0)
Công thức này hoạt động như sau:
Hàm EXACT đối chiếu giá trị tìm kiếm kiếm lookup_value với đa số giá trị vào phạm vi lookup_array. Nếu kiếm được giá trị đúng đắn hoàn toàn (phân biệt chữ hoa và chữ thường), hàm EXACT trả về cực hiếm TRUE. Ngược lại, giá bán trị nhận được là FALSE ví như trong phạm vi lookup_array không tồn tại giá trị nào khớp trọn vẹn với cực hiếm lookup_value.Hàm MATCH vào Excel sẽ thực hiện so sánh giá trị tìm kiếm TRUE với hiệu quả của hàm EXACT, tiếp nối trả về vị trí kha khá của cực hiếm trùng khớp đầu tiên.Lưu ý: phương pháp hàm MATCH bên trên là công thức mảng, bởi vì đó, chúng ta cần nhấn tổng hợp Ctrl+Shift+Enter để công thức chạy. Giả dụ không, Excel sẽ trả về lỗi #N/A.
Để các bạn hình dung trực quan hơn về cách sử dụng hàm MATCH vào trường hợp yêu cầu phân biệt chữ hoa và chữ thường, hãy tìm hiểu ngay một lấy ví dụ minh họa nhé. đưa sử họ có bảng tổng thích hợp số liệu kinh doanh phụ thuộc mã mặt hàng như vào hình.
Các mã hàng này là sự phối kết hợp của những ký trường đoản cú chữ hoa và chữ hay khác nhau. Vị đó, trường hợp chỉ áp dụng hàm MATCH thường thì để kiếm tìm ra địa điểm của một mã mặt hàng trong bảng thì kỹ năng cao bọn họ sẽ dấn về kết quả sai lệch. Để giải yêu mong này, chúng ta sẽ tạo cách làm hàm lấy cực hiếm của ô trong Excel phối hợp MATCH với EXACT như sau:
=MATCH(TRUE,EXACT(A2:A10,E2),0)
Trong đó: Hàm EXACT khẳng định yêu ước hàm MATCH search kiếm quý hiếm trong bảng với điều kiện khớp với cái giá trị buộc phải tìm lookup_value 100% lẫn cả về nội dung và hình thức chữ hoa, chữ thường.
Sự kết hợp công thức hàm search kiếm trong Excel từhàm MATCH cùng hàm EXACT đã cho ta biết sản phẩm có mã B-201 sẽ đứng ở đoạn thứ 6 trong bảng dữ liệu tổng hợp. Vậy là chúng ta không phải phải băn khoăn lo lắng về hàm tìm kiếm trong Excel biệt lập chữ hoa và chữ thường xuyên nữa rồi.
Các phép phối hợp hàm MATCH với các hàm kiếm tìm kiếm trong Excel
Sau khi đã khám phá về những ứng dụng của hàm MATCH, các bạn có tò mò về sự khác hoàn toàn của hàm này với những hàm tra cứu kiếm vào Excel khác? công dụng của toàn bộ các hàm bao gồm điểm gì khác biệt? vào trường hợp của chính mình thì nên sử dụng hàm nào? Hãy tò mò câu trả lời ngay thôi!
Hàm MATCH kết hợp hàm VLOOKUP
Nếu đã có lần sử dụng hàm VLOOKUP nhằm tham chiếu dữ liệu trang tính, vững chắc chắn các bạn sẽ thấy đó là một trong những hàm search kiếm trong Excel vô cùng đơn giản và nhanh chóng. Cùng với cú pháp ko mấy phức tạp, cách thức hoạt hễ không mất quá nhiều thời gian, hàm VLOOKUP hiện nay đang nắm vị trí đứng vị trí số 1 khi kể tới hàm tham chiếu thịnh hành trong Excel.
Vậy nhưng, sau đang quen với cách tìm kiếm dữ liệu bằng hàm VLOOKUP, tất cả thể các bạn sẽ nhận ra rằng hàm VLOOKUP đơn giản và dễ dàng là vậy, cơ mà lại có nhiều hạn chế. Trong số những hạn chế lớn số 1 của hàm này chính là việc nó không chuyển động sau khi bọn họ đã thêm hoặc bớt một số cột trong phạm vi tra cứu kiếm. Lí do bởi vì hàm VLOOKUP kiếm tìm kiếm dữ liệu dựa trên vị trí của cột mà bạn đã xác định từ trước. Vày đó, một lúc bạn thay đổi vị trí cột, hàm VLOOKUP vào Excel sẽ không thể nhận thấy và tự động điều chỉnh bí quyết được.
Để xung khắc phục giảm bớt này của hàm VLOOKUP, hàm MATCH chắc hẳn rằng sẽ là một trong trợ thủ trả hảo. Được thiết kế với trọng trách xác xác định trí kha khá của quý giá tìm kiếm, hàm MATCH trong Excel đó là sự lựa chọn hoàn hảo cho thông số col_index_num của hàm VLOOKUP. Nói một cách đối chọi giản, thay vì điền vào công thức hàm VLOOKUP một vị trí cột nuốm định, bạn cũng có thể sử dụng hàm MATCH - hàm lấy quý hiếm của ô, để xác định vị trí hiện tại của cột nên tham chiếu. Như vậy, hàm MATCHkết hòa hợp VLOOKUP sẽ giải quyết hoàn toàn trắc trở với việc sử dụng hàm tìm kiếm kiếm vào Excel sau khi biến hóa cấu trúc những cột bên trên trang tính.
Hãy khám phá một ví dụ như về bảng điểm học tập sinh. đưa sử ta có 2 đầu điểm Toán với Văn. Giá trị đề nghị tìm là điểm Văn của em học viên tên Nhung. Đầu tiên, chúng ta sẽ thực hiện công thức hàm VLOOKUP thông thường:
=VLOOKUP(F2,A2:C11,3,FALSE)
Tiếp theo, bọn họ xóa cột điểm Toán ngoài bảng. Như các bạn đã biết, thao tác làm việc này chắc chắn rằng sẽ làm thay đổi cấu trúc các cột trên trang tính Excel. Do đó, các bạn sẽ thấy lỗi #REF! hiện hữu tại ô F2. Lỗi này thông báo cho chúng ta rằng tham chiếu không hợp lệ, bắt nguồn từ việc công thức VLOOKUP hướng dẫn và chỉ định đến cột vật dụng 3 vào bảng, mặc dù nhiên, bảng điểm từ bây giờ chỉ còn sót lại 2 cột bởi đã xóa đi 1 cột trước đó.
Câu hỏi đặt ra là: Làm nắm nào nhằm xử lý vấn đề này? Hãy áp dụng công thức hàm MATCH phối hợp VLOOKUP chúng mình đã giới thiệu ở phía trên. Trước tiên, chúng ta có công thức hàm MATCH trong trường phù hợp này như sau:
=MATCH(D3,A1:C1,0)
Trong đó:
lookup_value = D3: giá chỉ trị đề nghị tìm đặt trong ô D3, là tên đúng chuẩn của cột Điểm Văn.lookup_array = A1:C1: Phạm vi search kiếm giá trị lookup_value là 3 title của bảng điểm.match_type = 0: tra cứu kiếm giá trị khớp hoàn toàn với lookup_value.Như vậy, hàm MATCH vẫn tìm kiếm và trả về đúng đắn vị trí của cột Điểm Văn, bất kể bạn thêm hay bớt từng nào cột vào trang tính Excel. Bọn họ lồng công thức hàm lấy cực hiếm của ô này vào tham số col_index_num nhằm xác xác định trí cột trong công thức hàm VLOOKUP:
=VLOOKUP(E2,A1:C11,MATCH(D3,A1:C1,0),FALSE)
Hãy test kiểm tra kết quả của phương pháp hàm MATCH kết hợp VLOOKUP này nhé.
Như các bạn đã thấy, không hề có sự khác biệt hay lỗi Excel nào xảy ra khi chúng ta áp dụng phép phối kết hợp hàm MATCH với VLOOKUP để chế tác một công thức hàm tìm kiếm trong Excel. Chính sự linh hoạt lợi sợ của hàm tham chiếu MATCH đã nâng tác dụng của hàm VLOOKUP lên một khoảng cao mới.
Hàm MATCH kết hợp hàm HLOOKUP
Tương từ bỏ như hàm VLOOKUP tìm kiếm kiếm ngang, hàm HLOOKUP kiếm tìm kiếm dọc cũng có thể có một nhược điểm cực nhọc ưa: Hàm sẽ ngừng hoạt rượu cồn sau khi người dùng thêm hoặc bớt những dòng trong trang tính Excel. Vậy là lại một lần nữa, họ cần đến sự linh hoạt của hàm MATCHđể kết phù hợp với một hàm tìm kiếm kiếm trong Excel.
Nếu như hàm MATCH vào Excel đang xuất sắc xong xuôi vai trò tham số col_index_num tại công thức hàm VLOOKUP, thì so với hàm HLOOKUP, cách áp dụng hàm MATCH phù hợp nhất là đưa vào tham số row_index_col. Nguyên tắc hoàn toàn có thể được đọc như phần giải thích của bọn chúng mình về trường hợp hàm VLOOKUP: Hàm MATCH sẽ auto xác định vị trí hàng chứa thông tin cần tham chiếu, thay vì chưng nhập địa điểm hàng cố định và thắt chặt tại tham số row_index_col. Vày đó, chúng ta sẽ tránh khỏi lỗi #REF! xẩy ra khi thay đổi cấu trúc những hàng Excel.
Vẫn liên tiếp với ví dụ về bảng điểm vào phần hàm MATCH phối hợp VLOOKUP, bọn họ sẽ tò mò công thức hàm tìm kiếm vào Excel hữu hiệu nhất lúc tham chiếu tài liệu trong bảng ngang.
Đến đây, có thể bạn sẽ do dự cách đưa bảng tự dọc thành ngang gấp rút mà không đề xuất nhập liệu từ đầu. Mẹo của chúng mình đối với thao tác này chính là copy cùng sử dụng tuấn kiệt Paste Special của Excel. Tham khảo nội dung bài viết dưới phía trên để search hiểu chi tiết nhé.
Quay quay trở về với ví dụ về hàm VLOOKUP, họ có cách làm hàm cơ phiên bản như sau:
=VLOOKUP(B5, B1:K3,3,FALSE)
Hàm VLOOKUP vận động trơn tru, không hề xảy ra vụ việc gì cho đến khi ta xóa đi dòng Điểm Toán. Tương tụ như với hàm VLOOKUP, họ lại nhận được lỗi #REF!
Khi bạn chạm chán phải lỗi này thì hãy nhờ mang đến hàm MATCH trong Excel coi sao nhé!
=MATCH(A6,A1:A3,0)
Trong đó:
lookup_value = A6: giá chỉ trị bắt buộc tìm để trong ô A6, là tên đúng đắn của sản phẩm Điểm Văn.lookup_array = A1:A3: Phạm vi tìm kiếm kiếm quý giá lookup_value là 3 title của bảng điểm.match_type = 0: kiếm tìm kiếm giá trị khớp hoàn toàn với lookup_value.Ghép bí quyết hàm MATCH trên vào thông số row_index_num của hàm HLOOKUP, họ có công thức hàm tìm kiếm kiếm trong Excel như sau:
=HLOOKUP(B5,B1:K3,MATCH(A6,A1:A3,0),FALSE)
Vậy là bằng phương pháp sử dụng hàm MATCH - hàm lấy cực hiếm của ô trong Excel, bọn họ ta đã thuận lợi khắc phục được tinh giảm của hàm các hàm LOOKUP. Tuy nhiên, hàm MATCH phối kết hợp VLOOKUP xuất xắc HLOOKUP chẳng thể hoàn toàn loại bỏ các nhược điểm của các hàm search kiếm trong Excel này. Nếu bạn cần một vẻ ngoài tham chiếu mạnh bạo hơn các hàm cơ bản, hãy cùng tìm hiểu về phép kết hợp tuyệt đối hoàn hảo của hàm MATCH và hàm INDEX ngay tiếp sau đây nhé!
Hàm MATCH kết hợp hàm INDEX
Chúng mình đã liệt kê những lý do bạn nên thực hiện hàm INDEX MATCH thay bởi vì hàm LOOKUP. Hàm INDEX trả về cực hiếm tại giao điểm một hàng với một cột. Hàmtìm kiếm trong Excel nàycó cú pháp như sau:
=INDEX (array, row_num,
Hãy cùng tìm hiểu phép phối hợp hàm MATCH cùng hàm INDEX qua từng trường hợp ví dụ nhé.
Tìm kiếm từ đề nghị sang tráiNếu sẽ quen với các hàm LOOKUP, có thể chắn bạn sẽ nhận ra những hàm tìm kiếm trong Excel này sẽ không thể kiếm tìm kiếm dữ liệu nếu quý giá cần tìm kiếm được đặt trong cột bên trái dải ô đang xác định. Bạn sẽ không còn phải lo lắng về nhược đặc điểm này của hàm LOOKUP nếu khách hàng sử dụng hàm INDEX MATCH.
Giả sử bọn họ có một bảng thông tin như sau:
Dựa trên dữ liệu của bảng tổng đúng theo này, yêu mong đề bài bác là xác định người chủ sở hữu của một email cụ thể. Vì chưng vị trí cột Họ và tên nằm cạnh trái cột Email, khôn xiết tiếc khi phải thông báo với bạn rằng hàm LOOKUP cấp thiết giúp gì vào trường đúng theo này. Thế vào đó, hàm MATCH vào Excelvà hàm INDEX sẽ trở nên trợ thủ đắc lực đến bạn.
Xem thêm: Cách Dùng Nước Tương Ofukuro, Nước Tương Ofukuro Nhật Bản Chai 100Ml
Hãy điền vào ô F3 cách làm hàm INDEX MATCH như sau:
=INDEX(A1:A6,MATCH(F2,C1:C6,0))
Trong đó:
Hàm MATCHtại tham số row_num có trọng trách tìm tìm vị trí kha khá của cực hiếm trong bảng khớp hoàn toàn với tên e-mail cần tìm kiếm tại ô F2.Hàm INDEX kiếm tìm kiếm giá trị nằmtrong phạm vi cột Họ với tên (cột A),tại vị trí tương xứng với công dụng hàm MATCH trong Excel.
Như vậy, bọn họ đã thành công xuất sắc tìm kiếm dữ liệu bằng cách sử dụng hàm INDEX với MATCH. Không tồn tại gì trở ngại phải không nào!
Tìm kiếm sau khi thêm hoặc xóa cột/hàngNhư vẫn đề cập trong phần trên, những hàm LOOKUP vẫn dừng vận động nếu bạn biến đổi cấu trúc trang tính Excel của mình. Bên cạnh phương pháp áp dụng hàm MATCH phối kết hợp VLOOKUP với HLOOKUP, bọn chúng mình sẽ khuyến nghị một giải pháp toàn diện hơn: hàm INDEX MATCH. Với hàm search kiếm trong Excel dạng này, cấu tạo trang tính Excel sẽ không làm ảnh hưởng đến hiệu quả công thức tham chiếu.
Hãy thử áp dụng công thức hàm MATCH và hàm INDEX vào lấy ví dụ như về bảng điểm họ đã khám phá với lấy ví dụ cách áp dụng hàm MATCH với các hàm LOOKUP nhé.
Công thức hàm INDEX cùng MATCH được áp dụng như sau:
=INDEX(C2:C11,MATCH(F2,A2:A11,0))
Trong đó:
Hàm MATCHtại thông số row_num có trọng trách tìm kiếm vị trí kha khá của giá trị trong bảng khớp hoàn toàn với tên học sinh cần search tại ô F2.Hàm INDEX search kiếm quý hiếm nằmtrong phạm vi cột học sinh (cột A),tại vị trí tương ứng với hiệu quả hàm MATCH vào Excel.
Ngay khi chúng ta thêm một cột điểm Anh vào bảng, phương pháp hàm INDEX MATCH sẽ tự động được update mà không hề phải sửa đổi thủ công. Điều này minh chứng tính linh hoạt đỉnh điểm của phương pháp hàm tìm kiếm kiếm trong Excel khi họ kết hợp hàm MATCH với hàm INDEX.
So với các công thức hàm INDEX MATCH chúng ta đã tra cứu hiểu, phép phối hợp hàm MATCH với hàm INDEX để tìm kiếm theo rất nhiều điều khiếu nại sẽ tinh vi hơn một chút. Tuy nhiên, đừng do vậy mà từ bỏ các bạn nhé! Bởi đó là một trường vừa lòng rất thông dụng mà rất tất cả thể các bạn sẽ gặp phải khi tham chiếu với xử lý dữ liệu trên Excel đấy.
Để thuận tiện hiểu được cách áp dụng hàm MATCH vào Excel cùng hàm INDEX với yêu mong tìm kiếm theo nhiều điều kiện, hãy xem ngay lập tức ví dụ sau đây nhé.
Giờ thì bạn hãy để ý trong các trường hợp hàm INDEX với MATCH trên, chỉ mãi sau một đk duy nhất để tham chiếu dữ liệu. Bởi đó, bọn họ chỉ sử dụng một cách làm hàm MATCH cùng với mục đích xác minh row_num, tức địa điểm hàng của giá chỉ trị đề xuất tìm.
Với ví dụ như này, họ cần trích xuất solo giá dựa trên 2 điều kiện: Mã hàng với mã công ty. Về bản chất vấn đề, họ cần áp dụng hàm INDEX để xác minh giá trị trên giao điểm của hàng cùng cột. Để làm cho được điều này, hãytạo 2 bí quyết hàm MATCHđóng sứ mệnh 2 hàm trả về quý giá của ô nhằm lần lượt điền vào những tham số row_num và col_num của hàm INDEX.
Bước 1: Tạo phương pháp hàm MATCH vào Excel xác định vị trí Mã doanh nghiệp trong bảng.=MATCH(H3,A3:A6,0)
Công thức này sẽ khẳng định tham số row_num vào hàm INDEX bằng cách trả về vị trí quý hiếm trong bảng khớp trọn vẹn với quý hiếm Mã công ty đặt tại ô H3.
Bước 2: Tạo phương pháp hàm MATCH vào Excel xác xác định trí Mã sản phẩm trong bảng.=MATCH(H2,A2:E2,0)
Công thức hàm MATCH đặt tại tham số col_num của hàm INDEX sẽ xác định vị trí cột đựng giá trị bằng đúng mực giá trị Mã hàng đã điền vào ô H2.
Bước 3: xong xuôi công thức hàm INDEX trả về giá chỉ trị nên tìmSau lúc đã chế tạo ra 2 bí quyết hàm MATCH lần lượt cho các tham số row_num cùng col_num, bọn họ sẽ hoàn thành xong công thức hàm INDEX như sau:
=INDEX(B3:E6,MATCH(H3,A3:A6,0),MATCH(H2,B2:E2,0))
Hàm INDEX vẫn phụ trách phần vấn đề cuối cùng, đó chính là tìm kiếm và trả về hiệu quả nằm trên giao điểm của hàng cùng cột dựa trên kết quả các bí quyết hàm MATCH trên tham số tương ứng.
Hàm kiếm tìm kiếm vào Excel như vào hình dường như hơi phức tạp một chút, tuy vậy hãy ghi nhớ kiến thức về cách sử dụng hàm INDEX cùng MATCH nhằm tìm kiếm nhiều điều kiện bạn nhé!
Tìm kiếm phối kết hợp tính toánMột điểm đặc biệt với hàm INDEX MATCH chính là khả năng phối hợp các hàm giám sát MIN, MAX và AVERAGE vào công thức hàm tra cứu kiếm trong Excel.
Giả sử chúng ta có bảng điểm tổng kết của một lớp học và đề xuất tìm các học sinh sở hữu số điểm cao nhấp, thấp nhất, đồng thời học sinh có số điểm ngay gần với mức độ vừa phải cả lớp nhất.
Để xác định học sinh có điểm tổng kết cao nhất trong bảng, bọn họ sử dụng hàm INDEX với MATCH với phương pháp như sau:
=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0))
Trong đó:
Hàm MATCH vào Excel đặt ở tham số row_num của hàm INDEX có nhiệm vụ xác xác định trí kha khá của giá chỉ trị lớn nhất trong phạm vi B2:B11.Hàm INDEX tiếp nối tham chiếu mang lại cột học sinh (A2:A11) nhằm tìm giá trị tương ứng với kết quả hàm MATCH.
Hoàn toàn tương tự, nhằm xác định học viên có số điểm tổng kết thấp tốt nhất trong bảng, chúng ta thiết lập cách làm hàm kiếm tìm kiếm trong Excel bên dưới đây:
=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0))
Khác cùng với cách xác minh các học sinh có điểm tổng kết cao và thấp nhất, bọn họ cần sắp xếp dữ liệu trong bảng về lắp thêm tự điểm tăng dần trước lúc áp dụng cách làm hàm MATCH phối hợp INDEX nhằm tìm học viên có số điểm ngay gần với vừa đủ lớp nhất.
Công thức được sử dụng trong trường hợp này là:
=INDEX(A2:A11,MATCH(AVERAGE(B2:B11),B2:B11,1))
Như chúng ta thấy, bọn họ sử dụng match_type = 1 so với hàm MATCH vào Excel. Đây là lý do bọn họ phải bố trí dữ liệu vào bảng để bảo đảm kết quả hàm INDEX MATCH trả về chính xác 100%.
Ngoài các công thức trên, phép kết hợp hàm MATCH với hàm INDEX còn vạc huy tác dụng tuyệt vời nếu bạn đang cách xử trí một chuỗi dữ liệu phức tạp với hàng vạn con số và các công thức đo lường và tính toán và logic. So vớicông thức hàm LOOKUP, chắc chắn là việc áp dụng hàm INDEX cùng MATCH vẫn đẩy nhanh vận tốc tìm kiếm của bạn hơn các lần.
Trong trường vừa lòng file Excel của người sử dụng chứa một kho dữ liệu ngổn ngang dẫn đến sự việc chạm tới giới hạn 255 ký kết tự của hàm VLOOKUP hay HLOOKUP, hãy đưa ngay sang dùng hàm INDEX MATCH nhé. Đây sẽ là phương án duy tốt nhất khả thi mang lại chuỗi tài liệu Excel khổng lồ của công ty đấy.
Với toàn bộ các tiện ích không thể chối gượng nhẹ của hàm INDEX cùng MATCH, thật không sai lúc nói rằng đây đó là một trong số những phép phối hợp mạnh mẽ và công dụng nhất giữa các hàm tra cứu kiếm vào Excel tính đến thời gian hiện tại. Chúng ta có gật đầu với đánh giá này không?
Hàm MATCH phối hợp hàm ISNA
Khi kết hợp với hàm ISNA, hàm MATCH lại đóng một sứ mệnh mới: Thám tử truy vết điểm trùng khớp và biệt lập giữa dữ liệu những cột vào bảng. Đầu tiên, chúng ta hãy tìm hiểu đôi điều về hàm ISNA trong Excel nhé.
Hàm ISNA giúp người tiêu dùng trả lời câu hỏi: giá trị này có chứa lỗi #N/A không?. Cú pháp hàm được mô tả vô cùng đơn giản như sau:
=ISNA(value)
Trong đó: value là quý hiếm cần xác minh kết quả. Quý hiếm này có thể là quý giá văn bản, cực hiếm số, cực hiếm ngày tháng hay 1 biểu thức logic, tham chiếu ô tính Excel.
Giờ thì bạn đã gắng được kiến thức cơ phiên bản về hàm ISNA rồi. Cùng thường xuyên với phương pháp phân biệt dữ liệu 2 cột nhé. Giả sử chúng ta cần khắc ghi các cực hiếm tại cột 2 không tồn trên trong cột 1, hãy sử dụng cú pháp hàm logic được tạo bởi vì hàm MATCH cùng hàm ISNA dưới đây:
=IF(ISNA(MATCH(giá trị trước tiên trong cột 1, cột 2, 0)), “Không bao gồm trong cột 1”, “”)
Công thức súc tích này vận động như sau:
Hàm MATCH vào Excel lần lượt đối chiếu từng giá trị ở cột 2 với toàn bộ các quý giá ở cột 1. Nếu giá trị tại cột 2 được search thấy trong cột 1, hàm MATCH sẽ trả về vị trí kha khá của cực hiếm đó. Ngược lại, ví như hàm cho rằng một giá trị tại cột 2 không tồn tại ở cột 1, hàm đang trả về lỗi #N/A.Hàm ISNA kiểm tra hiệu quả hàm MATCH có phải lỗi #N?A xuất xắc không. Nếu kết quả lỗi #N/A của hàm MATCH trong Excel vừa trả về là thiết yếu xác, tức là một quý giá ở cột 2 ko tồn tại ngơi nghỉ cột 1, hàm ISNA vẫn trả về TRUE. Ngược lại, trường hợp hàm tra cứu thấy cực hiếm đó trong cột 1, tác dụng trả về là FALSE. Các bạn hãy để ý kỹ phần này nhằm tránh lầm lẫn nhé.Nếu quý giá đang tham chiếu sinh sống cột 2 không tồn tại sinh sống cột 1, hàm IF sẽ trả về công dụng "Không bao gồm trong cột 1". Ngược lại, nếu giá trị đó xuất hiện ở cả hai cột, kết quả nhận được là cực hiếm rỗng. Bạn trọn vẹn có thể đổi khác các quý hiếm hiển thị này phụ thuộc vào ý say đắm của mình.Giả sử họ có 2 cột mã các khóa học tuyệt đỉnh Excel của edutainment.edu.vn như trong hình dưới đây:
Chúng ta sẽ áp dụng công thức hàm tìm kiếm kiếm vào Excel vừa học để phân biệt các dữ liệu khác nhau giữa 2 cột, cụ thể là đối chiếu toàn cục giá trị cột 2 với mức giá trị tương xứng trong cột 1.
=IF(ISNA(MATCH(B2,A:A,0)),"Không có trong cột 1","")
Sau khi vận dụng AutoFill cho toàn bộ các hàng dữ liệu trong bảng, họ nhận được kết quả: cực hiếm "EXG05" và cực hiếm "EXG06" chỉ mở ra trong cột 2 mà không hề tồn tại trong cột 1. Như vậy, bọn họ không đề xuất mất thời gian so sánh sự biệt lập này bằng mắt nữa.
Với ví dụ này, bọn họ mới tạm dừng ở mức độ rõ ràng nội dung những cột, chứ còn chưa tính đến dạng chữ. Vậy trong trường hợp đề nghị phân biệt chữ hoa và chữ thường, bọn họ sẽ làm nắm nào? Hãy áp dụng thêm hàm EXACT vào bí quyết hàm MATCH như chúng tôi đã đề cập trong số phần đầu bài xích nhé.
Lúc này, bí quyết hàm kiếm tìm kiếm trong Excel dài hơn một chút như sau:
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),“Không có trong cột 1”, “”)
Bạn hoàn toàn có thể thấy công thức xúc tích này là sự việc kết hợp của khá nhiều hàm tham chiếu trong Excel, mặc dù nhiên,không có gì đáng lúng túng cả. Biện pháp thức buổi giao lưu của các hàm hoàn toàn tương từ bỏ với hàm IF bọn họ vừa tìm kiếm hiểu. Điểm biệt lập duy tuyệt nhất nằm ở cấu tạo hàm MATCH được lồng thêm hàm EXACT để rất có thể phân biệt chữ hoa cùng chữ thường. Đừng quên rằngbạn có thể thay đổi các nhiều từ kết quả "Không bao gồm trong cột 1" hoặc cực hiếm rỗng "" bằng những cụm từ hiệu quả khác dễ dàng cho yêu cầu xử lý dữ liệu của mình.
Lưu ý: Đây là cách làm mảng, do đó, hãy ghi ghi nhớ rằng bạn cần nhấn tổ hợp phím Ctrl+Shift+Enter để Excel chạy hàm cùng trả về tác dụng đúng.
Bạn đã sớm nắm rõ cách sử dụng công thức hàm MATCH vào Excel kết hợp các hàm ngắn gọn xúc tích ngay khi bọn họ xem qua một ví dụ ví dụ trong hình bên dưới đây.
Việc vạc hiện những điểm khác hoàn toàn giữa 2 cột dữ liệu này bên cạnh đó rất trở ngại nếu ta chỉ cần sử dụng mắt. Chũm vào đó, tại sao không dùng đến công thức hàm logic chúng ta vừa tò mò nhỉ?
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),"Không bao gồm trong cột 1","")
Công thức này sẽ xác định các quý giá tồn tại sinh sống cột 2 mà không có trong cột 1, tựa như như với lấy một ví dụ trên. Hãy xem hình dưới đây và cùng chất vấn liệu tác dụng có đúng đắn như chúng ta mong ngóng không nhé.
Như vậy, những giá trị duy nhất tại cột gấp đôi lượt là "EXG01", "exg01" với "ExG02". Các bạn cũng có thể kiểm tra lại kết quả này để phòng ngừa trường hợp xẩy ra sai sót đáng tiếc nhé. Đừng quên xem xét lại kỹ phương pháp hàm của bản thân trước khi chạy. Bạn sẽ không ý muốn lục lại từng vệt phẩy vào công thức của bản thân khi vạc hiện kết quả trả về xảy ra lệch lạc đâu.
Bên cạnh cách sử dụng hàm MATCH phối kết hợp hàm ISNA để khẳng định các giá chỉ trị khác biệt giữa các cột trong bảng, họ còn bao gồm một cách thức khác cùng với hàm ROWS cùng COLUMNS. Ko chỉ tạm dừng ở đó, chúng ta còn hoàn toàn có thể dễ dàng phát hiện nay sự chênh lệch giữa số lượng ký từ bỏ giữa giá bán trị trong số cột. Hãy khám phá cách thực hiện trong nội dung bài viết dưới đây nhé!
Tổng kết
Trong bài viết ngày hôm nay, chúng ta đã tò mò về hàm MATCH vào Excel và các ứng dụng cực kì lợi sợ hãi của hàm MATCH với sứ mệnh là hàm lấy giá trị của ô trong Excel. Lợi thế của hàm tham chiếu MATCH đó là sự phối hợp linh hoạt và công dụng của nó với những hàm tra cứu kiếm vào Excel. Hy vọng qua bài viết của chúng mình, bạn đã vắt được cách thực hiện hàm MATCH thông minh để xử lý các trường hợp cụ thể.
Bên cạnh biện pháp sử dụnghàm MATCH, còn tương đối nhiều các hàm Excel khác vẫn chờ các bạn khám phá. Hãy xem thêm các nội dung bài viết trên blog edutainment.edu.vn nhằm tích lũy kỹ năng về kho tàng công thức rất nhiều của Excel các bạn nhé. Nếu như khách hàng đang tìm kiếm kiếm một khóa huấn luyện Excel, thuộc tham gia khóa đào tạo Tuyệt đỉnh Excel với bọn chúng mình tức thì thôi!
