Bạn có thể biến đổi hàm Match thành Vlookup hoặc Hlookup trong Google Sheets. Điều này có nghĩa là sử dụng hàm Match như một phương pháp thay thế cho các hàm Vlookup hoặc Hlookup.
Có lẽ tôi là người đầu tiên chia sẻ mẹo này trực tuyến. Trên thực tế, trong quá trình nghiên cứu, tôi không tìm thấy ai sử dụng mẹo này cho đến nay.
Bạn có thể thấy sự kết hợp của hàm Index-Match. Tuy nhiên, ở đây, tôi không sử dụng hàm Index. Vì vậy, công thức của tôi có thể được coi là duy nhất.
Hàm Match có thể tìm kiếm một khóa và trả về không chỉ vị trí tương đối của nó, mà còn có thể trích xuất giá trị từ hàng (Vlookup) hoặc cột (Hlookup) mà chứa khóa tìm kiếm.
Có lợi ích nào khi sử dụng hàm Match như một phương pháp thay thế cho Vlookup hoặc Hlookup?
Có! Có hai lợi ích.
-
Kết hợp Match + Indirect có thể tìm kiếm bên trái hoặc bên phải của khóa tìm kiếm.
-
Hữu ích trong Định dạng có điều kiện (Trong đó Vlookup có thể gặp khó khăn).
Hãy xem làm thế nào để biến đổi hàm Match thành Vlookup hoặc Hlookup trong Google Sheets và cả trong Excel. Sau đó, tôi sẽ giải thích hai điểm trên (lợi ích) khi sử dụng Match như một phương pháp thay thế cho Vlookup.
Cách biến đổi hàm Match thành Vlookup hoặc Hlookup trong Google Sheets / Excel
Dưới đây là hướng dẫn từng bước để sử dụng Match như một phương pháp thay thế cho Vlookup.
Dữ liệu mẫu: Sản xuất táo theo quốc gia
Phạm vi dữ liệu: B4: C18
Nguồn: Wiki
Dữ liệu này được nhập vào Google Sheets bằng cách sử dụng hàm IMPORTHTML để kiểm tra hàm Match.
Nó sẽ không hoạt động trên Excel. Vì vậy, nếu bạn đang sử dụng Excel, hãy gõ dữ liệu hoặc nhập dữ liệu vào Google Sheets và sau đó sao chép vào Excel.
Đây là công thức mà tôi đã sử dụng trong ô B4 trong Google Sheets của mình để nhập dữ liệu này để kiểm tra.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_apple_production","table",1)
Hình ảnh:
Dưới đây là hướng dẫn từng bước để biến đổi hàm Match thành Vlookup. Tiếp theo, tôi sẽ hướng dẫn bạn các bước liên quan đến Hlookup. Nó không phức tạp, vì vậy hãy tiếp tục đọc.
Các bước để biến đổi hàm Match thành Vlookup trong Google Sheets / Excel
Mục đích: Tìm kiếm khóa tìm kiếm “Pháp” trong cột B và trả về “Sản lượng táo (tấn)” của nó từ cột C. Kết quả phải là giá trị 1.819.762.
Đây là công thức Vlookup thông thường:
=VLOOKUP(E4,B5:C18,2,0)
Công thức Match đã được biến đổi:
=INDIRECT("C"&MATCH(E4,B1:B18,0))
Như tôi đã đề cập ở trên, công thức kết hợp Match này có một số lợi thế hơn Vlookup.
Không có gì để nghi ngờ rằng Vlookup mạnh mẽ hơn và khả năng trả về một mảng kết quả không thể sánh bằng.
Công thức Match giải thích và các bước:
Bước 1: Sử dụng hàm Match duy nhất trong Google Sheets trong bộ dữ liệu trên.
Xem ví dụ công thức Match dưới đây trả về vị trí tương đối của khóa tìm kiếm “Pháp” trong phạm vi B5: B18.
Để biến đổi hàm Match thành Vlookup, bước đầu tiên là tìm số hàng, chứ không phải vị trí tương đối của khóa tìm kiếm.
Làm thế nào để chuyển đổi vị trí tương đối thành số hàng thực tế?
Trong công thức Match, phạm vi là B5: B8. Chỉ cần thay đổi phạm vi thành B1: B8. Nó sẽ chuyển đổi vị trí tương đối trả về bởi Match thành số hàng.
=MATCH(E4,B1:B18,0)
Công thức này sẽ trả về 13, đó là số hàng của khóa tìm kiếm “Pháp” trong cột B. Đây là Bước 1.
Bước 2: Chuyển đổi Match thành Vlookup.
Chúng tôi muốn công thức tìm kiếm “Pháp” và trả về “Sản lượng táo (tấn)” của nó trong Cột C.
Thực ra, điều chúng tôi muốn là trích xuất nội dung trong ô C13. Vui lòng tham khảo dữ liệu mẫu của tôi ở trên.
Vì vậy, hãy thêm tiền tố cho chữ cái cột này vào đầu đầu ra Match đó chính là số hàng 13.
="C"&MATCH(E4,B5:B18,0)
Kết quả: C13
Bước 3: Indirect ô này.
=INDIRECT("C"&MATCH(E4,B1:B18,0))
Xong!
Các bước để biến đổi hàm Match thành Hlookup trong Google Sheets / Excel
Ở đây, các bước có một chút khác nhau vì chúng ta sử dụng Match trong một hàng.
Công thức Match dưới đây sẽ trả về vị trí tương đối của khóa tìm kiếm “Pháp” trong hàng.
Ở đây, chúng ta có thể sửa đổi phạm vi dữ liệu trong công thức Match để trả về số cột của khóa tìm kiếm.
Ở đây, công thức Match trả về 10, đó là số cột tương đối. Tôi đã chia sẻ với bạn trước đó cách chuyển đổi vị trí tương đối thành số hàng ở trên.
Ở đây, chúng ta có thể chuyển đổi vị trí tương đối thành số cột vì đây là một trận ngang.
Dưới đây là công thức đó. Chỉ cần thay đổi phạm vi tìm kiếm từ B4: P4 thành A4: P4.
=match("France",A4:P4,0)
Công thức này trả về 11, đó là số cột. Bạn có thể chuyển đổi số cột thành chữ cái cột trong Google Sheets bằng cách sử dụng hàm Char như dưới đây.
Chỉ cần sử dụng =Char(64+1) để trả về chữ cái cột A. Nếu bạn thay thế 1 bằng 26, nó sẽ trả về Z.
Điều đó có nghĩa là chúng ta có thể cộng số cột để có chữ cái cột. Vì vậy, công thức sẽ là:
=Char(64+match("France",A4:P4,0))
Kết quả: K
Lưu ý: Công thức này có giới hạn. Nó chỉ hoạt động cho đến cột Z. Nếu phạm vi dữ liệu của bạn lớn hơn, bạn có thể sử dụng công thức thay thế này. Điều này chỉ hoạt động trong Google Sheets.
=regexreplace(address(1,match("France",A4:P4,0)),"[^A-Z]","")
Trong công thức này, hàm Match được bao quanh bởi các yếu tố khác để thay thế vai trò của hàm Char.
Giá trị cần trích xuất là ở hàng số 5. Điều đó có nghĩa là bạn muốn giá trị trong ô K5. Vì vậy, hãy kết hợp số 5 với công thức trên.
=char(64+match("France",A4:P4,0))&5
Rồi Indirect nó.
=indirect(char(64+match("France",A4:P4,0))&5)
Bạn đã học được hai mẹo tuyệt vời để biến đổi hàm Match thành Vlookup và Hlookup trong Google Sheets / Excel. Bạn đã từng thấy điều này được sử dụng trước đây chưa?
Biến đổi hàm Match thành Vlookup – Lợi ích
Ở đây, tôi sẽ không đi vào Hlookup vì Vlookup là phổ biến nhất.
Tôi đã đề cập trước đó lợi ích của việc sử dụng hàm Match cho việc tìm kiếm dọc / ngang. Có hai lợi ích rõ ràng.
Tìm kiếm bên trái của khóa tìm kiếm bằng cách sử dụng Match
Trong ví dụ này, tôi muốn tìm xếp hạng “Sinh viên 3”. Khóa tìm kiếm “Sinh viên 3” nằm trong cột E và xếp hạng nằm trong cột D, rõ ràng là phía trái cột tìm kiếm.
Dĩ nhiên, bạn có thể sử dụng Vlookup hoặc Indirect-Match trong trường hợp này. Tôi không phủ nhận điều đó. Nhưng cách sử dụng Match này khá tuyệt.
Định dạng có điều kiện
Vì chúng tôi tạo địa chỉ ô và sau đó Indirect nó, điều đó rất hữu ích trong Định dạng có điều kiện.
Hãy xem hướng dẫn nâng cao này để hiểu rõ hơn – Tô sáng giá trị giao nhau trong Google Sheets trong tìm kiếm hai chiều.
Hy vọng bạn đã học được cách biến đổi hàm Match thành Vlookup / Hlookup trong Google Sheets và cả trong Excel.
Hãy học mẹo tuyệt vời này trước và sau đó chia sẻ thông tin mới này với bạn bè của bạn. Thưởng thức!
