Bạn có thể sử dụng hàm MATCH trong Excel để tìm vị trí tương đối của một giá trị cụ thể trong một phạm vi ô hoặc một mảng.
Hàm MATCH tương tự như hàm Vlookup vì cả hai đều được phân loại trong Hàm Tra cứu / Tham chiếu Excel. Hàm VLOOKUP tìm kiếm một giá trị cụ thể trong một cột và trả về một giá trị trong cùng một hàng trong khi hàm MATCH tìm kiếm một giá trị nhất định trong một phạm vi và trả về vị trí của giá trị đó.
Hàm MATCH trong Excel tìm kiếm một giá trị được chỉ định trong một phạm vi ô hoặc một mảng và trả về vị trí tương đối của lần xuất hiện đầu tiên của giá trị đó trong phạm vi. Hàm MATCH cũng có thể được sử dụng để tra cứu một giá trị nhất định và trả về giá trị tương ứng của nó với sự trợ giúp của hàm INDEX (giống như Vlookup). Hãy xem cách sử dụng hàm MATCH trong Excel để tìm vị trí của giá trị tra cứu trong một phạm vi ô.
Hàm MATCH trong Excel
Hàm MATCH là một hàm tích hợp sẵn trong Excel và được sử dụng chủ yếu để định vị vị trí tương đối của giá trị tra cứu trong một cột hoặc một hàng.
Cú pháp của hàm MATCH:
= MATCH (lookup_value, lookup_array, [match_type})Ở đâu:
lookup_value - Giá trị bạn muốn tra cứu trong một phạm vi ô xác định hoặc trong một mảng. Nó có thể là giá trị số, giá trị văn bản, giá trị lôgic hoặc tham chiếu ô có giá trị.
lookup_array - Mảng ô mà bạn đang tìm kiếm một giá trị. Nó phải là một cột hoặc một hàng.
Loại so khớp - Đây là một tham số tùy chọn có thể được đặt thành 0,1 hoặc -1 và mặc định là 1.
- 0 tìm kiếm một kết quả phù hợp chính xác, khi không tìm thấy, sẽ trả về một lỗi.
- -1 tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value khi mảng tra cứu theo thứ tự tăng dần.
- 1 tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị look_up khi mảng tra cứu theo thứ tự giảm dần.
Tìm vị trí của một kết hợp chính xác
Giả sử, chúng ta có tập dữ liệu sau mà chúng ta muốn tìm vị trí của một giá trị nhất định.
Trong bảng này, chúng tôi muốn tìm vị trí của tên thành phố (Memphis) trong cột (A2: A23), vì vậy chúng tôi sử dụng công thức này:
= MATCH ("memphis", A2: A23,0)Đối số thứ ba được đặt thành '0' vì chúng tôi muốn tìm một kết quả khớp chính xác của tên thành phố. Như bạn có thể thấy rằng tên thành phố “memphis” trong công thức là chữ thường trong khi trong bảng chữ cái đầu tiên của tên thành phố là chữ hoa (Memphis). Tuy nhiên, công thức có thể tìm thấy vị trí của giá trị được chỉ định trong phạm vi đã cho. Đó là vì hàm MATCH không phân biệt chữ hoa chữ thường.
Ghi chú: Nếu không tìm thấy giá trị lookup_value trong phạm vi tra cứu hoặc nếu bạn chỉ định phạm vi tra cứu sai, hàm sẽ trả về lỗi # N / A.
Bạn có thể sử dụng tham chiếu ô trong đối số đầu tiên của hàm thay vì giá trị trực tiếp. Công thức dưới đây tìm vị trí của giá trị trong ô F2 và trả về kết quả trong ô F3.
Tìm vị trí của một kết hợp gần đúng
Có hai cách bạn có thể tìm kiếm giá trị tra cứu gần đúng hoặc khớp chính xác và trả về vị trí của nó.
- Một cách là tìm giá trị nhỏ nhất lớn hơn hoặc bằng (khớp lớn nhất tiếp theo) với giá trị được chỉ định. Nó có thể đạt được bằng cách đặt đối số cuối cùng (match_type) của hàm là ‘-1’
- Một cách khác là giá trị lớn nhất nhỏ hơn hoặc bằng (khớp nhỏ nhất tiếp theo) với giá trị đã cho. Nó có thể đạt được bằng cách đặt match_type của hàm là '1'
Trận đấu nhỏ nhất tiếp theo
Nếu hàm không thể tìm thấy đối sánh chính xác với giá trị được chỉ định khi loại đối sánh được đặt thành '1', thì hàm sẽ định vị giá trị lớn nhất nhỏ hơn một chút so với giá trị được chỉ định (có nghĩa là giá trị nhỏ nhất tiếp theo) và trả về vị trí của nó . Để làm được điều này, bạn cần sắp xếp mảng theo thứ tự tăng dần, nếu không nó sẽ dẫn đến lỗi.
Trong ví dụ, chúng tôi sử dụng công thức dưới đây để tìm kết quả khớp nhỏ nhất tiếp theo:
= MATCH (F2, D2: D23,1)Khi công thức này không thể tìm thấy kết quả khớp chính xác cho giá trị trong ô F2, nó sẽ trỏ đến vị trí (16) của giá trị nhỏ nhất tiếp theo, tức là 98.
Trận đấu lớn nhất tiếp theo
Khi loại đối sánh được đặt thành ‘-1’ và hàm MATCH không thể tìm thấy đối sánh chính xác, hàm này sẽ tìm giá trị nhỏ nhất lớn hơn giá trị được chỉ định (có nghĩa là giá trị lớn nhất tiếp theo) và trả về vị trí của nó. Mảng tra cứu phải được sắp xếp theo thứ tự giảm dần cho phương thức này nếu không nó sẽ trả về lỗi.
Ví dụ: nhập công thức sau để tìm kết quả khớp lớn nhất tiếp theo với giá trị tra cứu:
= MATCH (F2, D2: D23, -1)Hàm MATCH này tìm kiếm giá trị ở F2 (55) trong phạm vi tra cứu D2: D23 và khi không thể tìm thấy kết quả khớp chính xác, hàm này sẽ trả về vị trí (16) của giá trị lớn nhất tiếp theo, tức là 58.
Đối sánh ký tự đại diện
Các ký tự đại diện chỉ có thể được sử dụng trong hàm MATCH khi match_type được đặt thành ‘0’ và giá trị tra cứu là một chuỗi văn bản. Có những ký tự đại diện bạn có thể sử dụng trong hàm MATCH: dấu hoa thị (*) và dấu chấm hỏi (?).
- Dấu chấm hỏi (?) được sử dụng để so khớp bất kỳ ký tự hoặc chữ cái đơn lẻ nào với chuỗi văn bản.
- Dấu hoa thị (*) được sử dụng để so khớp bất kỳ số ký tự nào với chuỗi.
Ví dụ: chúng tôi đã sử dụng hai ký tự đại diện ‘?’ Trong lookup_value (Lo ?? n) của hàm MATCH để tìm một giá trị khớp với chuỗi văn bản có hai ký tự bất kỳ (trong các ký tự đại diện ở vị trí). Và hàm trả về vị trí tương đối của giá trị khớp trong ô E5.
= MATCH ("Lo ?? n", A2: A22,0)
Bạn có thể sử dụng ký tự đại diện (*) giống như (?), Nhưng dấu hoa thị được sử dụng để khớp với bất kỳ số ký tự nào trong khi dấu chấm hỏi được sử dụng để khớp với bất kỳ ký tự đơn lẻ nào.
Ví dụ: nếu bạn sử dụng ‘sp *’, hàm có thể khớp với loa, tốc độ hoặc spielberg, v.v. Nhưng nếu hàm tìm thấy nhiều giá trị / trùng lặp khớp với giá trị tra cứu, thì hàm sẽ chỉ trả về vị trí của giá trị đầu tiên.
Trong ví dụ, chúng tôi đã nhập “Kil * o” trong đối số lookup_value. Vì vậy, hàm MATCH () tìm kiếm văn bản có chứa ‘Kil’ ở đầu, ‘o’ ở cuối và bất kỳ số ký tự nào ở giữa. ‘Kil * o’ khớp với Kilimanjaro trong mảng và do đó hàm trả về vị trí tương đối của Kilimanjaro, là 16.
INDEX và MATCH
Các hàm MATCH hiếm khi được sử dụng một mình. Chúng thường được ghép nối với các hàm khác để tạo ra các công thức mạnh mẽ. Khi hàm MATCH được kết hợp với hàm INDEX, nó có thể thực hiện tra cứu nâng cao. Nhiều người vẫn thích sử dụng hàm VLOOKUP để tra cứu giá trị vì nó đơn giản hơn nhưng INDEX MATCH linh hoạt hơn và nhanh hơn so với hàm VLOOKUP.
VLOOKUP chỉ có thể tra cứu một giá trị theo chiều dọc, tức là các cột trong khi tổ hợp INDEX MATCH có thể thực hiện cả tra cứu theo chiều dọc và chiều ngang.
Hàm INDEX được sử dụng để truy xuất một giá trị tại một vị trí cụ thể trong một bảng hoặc một dải ô. Hàm MATCH trả về vị trí tương đối của một giá trị trong một cột hoặc một hàng. Khi được kết hợp, MATCH tìm số hàng hoặc cột (vị trí) của một giá trị cụ thể và hàm INDEX lấy ra một giá trị dựa trên số hàng và cột đó.
Cú pháp của hàm INDEX:
= INDEX (mảng, row_num, [col_num],)Dù sao, hãy xem cách INDEX MATCH hoạt động với một ví dụ.
Trong ví dụ dưới đây, chúng tôi muốn truy xuất điểm ‘Quiz2’ cho học sinh ‘Anne’. Để làm điều đó, chúng tôi sẽ sử dụng công thức dưới đây:
= INDEX (B2: F20, MATCH (H2, A2: A20,0), 3)INDEX cần một số hàng và cột để truy xuất một giá trị. Trong công thức trên, hàm MATCH lồng nhau tìm số hàng (vị trí) của giá trị ‘Anne’ (H2). Sau đó, chúng tôi cung cấp số hàng đó cho hàm INDEX với phạm vi B2: F20 và số cột (3), mà chúng tôi chỉ định. Và hàm INDEX trả về điểm '91'.
Tra cứu hai chiều với INDEX và MATCH
Bạn cũng có thể sử dụng hàm INDEX và MATCH để tra cứu giá trị trong phạm vi hai chiều (tra cứu hai chiều). Trong ví dụ trên, chúng tôi đã sử dụng hàm MATCH để định vị số hàng của một giá trị, nhưng chúng tôi đã nhập số cột theo cách thủ công. Nhưng chúng ta có thể tìm thấy cả hàng và cột bằng cách lồng hai hàm MATCH, một trong đối số row_num và một trong đối số column_num của hàm INDEX.
Sử dụng công thức này để tra cứu hai chiều với INDEX và MATCH:
= INDEX (A1: F20, MATCH (H2, A2: A20,0), MATCH (H3, A1: F1,0))Như chúng ta đã biết, hàm MATCH có thể tìm kiếm một giá trị theo cả chiều ngang và chiều dọc. Trong công thức này, hàm MATCH thứ hai trong đối số colum_num tìm vị trí của Quiz2 (4) và cung cấp nó cho hàm INDEX. Và INDEX truy xuất điểm số.
Bây giờ, bạn đã biết cách sử dụng hàm Match trong Excel.