Trong Google Sheets, hàm VLOOKUP mặc định không phân biệt chữ hoa và chữ thường. Tuy nhiên, bạn vẫn có thể áp dụng công thức VLOOKUP có phân biệt chữ hoa chữ thường. Làm thế nào?
Bạn có thể sử dụng các hàm bổ sung kết hợp với VLOOKUP để thực hiện phân biệt chữ hoa chữ thường. Các hàm như CODE, EXACT và REGEXMATCH sẽ giúp bạn thực hiện tìm kiếm phân biệt chữ hoa chữ thường. Bạn cũng có thể sử dụng các hàm khác như XLOOKUP, FILTER hoặc INDEX-MATCH.
Dưới đây là một số ví dụ và cách sử dụng các hàm này.
Dữ liệu mẫu và công thức VLOOKUP không phân biệt chữ hoa chữ thường
Trong cột A, bạn có các ID sản phẩm, cột B chứa các mô tả tương ứng và cột C hiển thị số lượng có sẵn cho mỗi sản phẩm. Mục tiêu của bạn là thực hiện tìm kiếm dọc trong bảng này.
Bạn sẽ nhận thấy rằng một số sản phẩm có cùng ID, và để phân biệt chúng dựa trên cách làm, tôi đã sử dụng chữ hoa “A” và chữ thường “a”.
Ví dụ, “SH_101A” và “SH_101a” đại diện cho cùng một sản phẩm, đó là một mũ bảo hiểm, nhưng chúng có sự khác biệt trong cách làm.
Bạn cần tìm kiếm một sản phẩm và trả về tên sản phẩm của nó. Dưới đây là công thức chuẩn khi điều kiện tìm kiếm (search_key) được chỉ định trong ô E2:
=VLOOKUP(E2, A2:C, 2, 0) // trả về mô tả
Cú pháp:
=VLOOKUP(search_key, range, index, [is_sorted])
Tuy nhiên, kết quả không chính xác vì công thức này không phân biệt chữ hoa chữ thường.
Trước tiên, hãy tìm hiểu cách sử dụng VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets. Sau đó, chúng ta sẽ khám phá các phương pháp thay thế phổ biến.
Công thức VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets
Chúng ta sẽ sử dụng ba hàm để thực hiện tìm kiếm phân biệt chữ hoa chữ thường trong VLOOKUP: EXACT, REGEXMATCH và CODE.
Hãy xem từng hàm một cách chi tiết.
Sử dụng hàm EXACT với VLOOKUP
Thường chúng ta sử dụng hàm EXACT để kiểm tra xem hai chuỗi có giống nhau không. Tuy nhiên, chúng ta cũng có thể sử dụng nó để so sánh một chuỗi với một danh sách các chuỗi. Trong trường hợp sau, chúng ta cần sử dụng hàm ArrayFormula kết hợp với EXACT.
Ví dụ, công thức sau đây sẽ trả về một mảng giá trị TRUE hoặc FALSE, cho biết vị trí nơi tìm thấy sự khớp phân biệt chữ hoa chữ thường:
=ArrayFormula(EXACT(E2, A2:A))
Ở đây, chúng ta sử dụng EXACT để xác định sự khớp phân biệt chữ hoa của search_key trong cột đầu tiên của range.
Trong phạm vi VLOOKUP A2:C, chúng ta cần thay thế A2:A bằng kết quả này. Làm thế nào để làm được điều đó?
Chúng ta sẽ tạo một phạm vi ảo (virtual range) cho VLOOKUP.
Để tạo ra một phạm vi ảo cho VLOOKUP, chúng ta có thể sử dụng Dấu ngoặc nhọn hoặc HSTACK. Trong công thức VLOOKUP có phân biệt chữ hoa chữ thường này, phạm vi sẽ được định nghĩa là {exact_result, B2:C}.
Dưới đây là công thức VLOOKUP có phân biệt chữ hoa chữ thường:
=ArrayFormula(VLOOKUP(TRUE, {EXACT(E2, A2:A), B2:C}, 2, 0))
Ở đây, search_key là TRUE, không phải E2, vì cột đầu tiên trong phạm vi giờ chứa chỉ giá trị TRUE hoặc FALSE.
Tôi khuyên bạn nên sử dụng phương pháp này để thực hiện VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets.
Lưu ý: Nếu search_key của bạn là số hoặc ngày thì nó nên được định dạng tương tự như cột đầu tiên (mảng tìm kiếm) trong phạm vi.
Sử dụng hàm REGEXMATCH với VLOOKUP
Đây là tương tự như ví dụ trước. Ở đây, chúng ta sẽ thay thế hàm EXACT bằng hàm REGEXMATCH.
Thay thế EXACT(E2, A2:A) bằng REGEXMATCH(A2:A, "^" & E2 & "$").
Công thức:
=ArrayFormula(VLOOKUP(TRUE, {REGEXMATCH(A2:A, "^"&E2&"$"), B2:C}, 2, 0))
REGEXMATCH sử dụng biểu thức chính quy để khớp mẫu và cũng trả về giá trị TRUE hoặc FALSE. Nó có một lợi thế nhỏ hơn so với công thức trước đó. Đó là gì?
Bạn có thể chuyển đổi VLOOKUP có phân biệt chữ hoa chữ thường thành không phân biệt chữ hoa bằng cách chỉnh sửa biểu thức chính quy từ "^" & E2 & "$" thành "(?i)^" & E2 & "$".
Ngoài ra, bạn có thể thích nghi với việc tìm kiếm một phần của khóa tìm kiếm bằng cách thay đổi biểu thức từ "^" & E2 & "$" thành chỉ E2.
Sử dụng hàm CODE với VLOOKUP
Tôi không thích phương pháp này, nhưng tôi đưa nó vào đây để giúp bạn hiểu các công thức VLOOKUP được tạo bởi người khác.
Trong phương pháp VLOOKUP có phân biệt chữ hoa chữ thường này, chúng ta sử dụng giá trị bản đồ Unicode của search_key để tìm khớp trong cột đầu tiên của phạm vi, cũng chứa giá trị bản đồ Unicode.
Lý do tôi không ưa thích phương pháp này là hàm CODE chỉ trả về giá trị bản đồ Unicode của ký tự đầu tiên trong chuỗi. Do đó, chúng ta cần trích xuất từng ký tự, lấy mã và nối chúng. Phương pháp này cũng yêu cầu sử dụng cột trợ giúp, làm cho nó ít linh hoạt hơn.
Dưới đây là cách nó hoạt động:
Chèn công thức sau vào ô D2 và kéo xuống tới ô D5:
=ArrayFormula(JOIN("",CODE(MID(A2, SEQUENCE(LEN(A2)), 1)))
Lưu ý: Trong một bài hướng dẫn trước đó, tôi đã giải thích cách trích xuất từng ký tự từ một chuỗi. Bạn có thể tham khảo hướng dẫn đó: Công thức để đảo ngược văn bản và số trong Google Sheets
Kết quả của công thức trên sẽ trở thành cột đầu tiên trong phạm vi, vì vậy phạm vi sẽ là {D2:D, B2:C}. Chúng ta cần chuyển đổi khóa tìm kiếm theo cách tương tự bằng cách sử dụng công thức đã đề cập ở trên.
Công thức VLOOKUP sẽ trông như sau:
=VLOOKUP(ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1)))), {D2:D, B2:C}, 2, 0)
Trong đó:
Search_key:ArrayFormula(JOIN("",CODE(MID(E2, SEQUENCE(LEN(E2)), 1))))range:{D2:D, B2:C}index: 2is_sorted: 0
Các phương pháp thay thế cho VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets
Tôi đã đề cập đến việc sử dụng một số phương pháp thay thế để thực hiện VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets. Các phương pháp này đều yêu cầu sử dụng các hàm hỗ trợ, và sự lựa chọn của tôi là EXACT.
Công thức #1: XLOOKUP
=ArrayFormula(XLOOKUP(TRUE, EXACT(E2, A2:A), B2:B))
Cú pháp của hàm XLOOKUP:
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Công thức XLOOKUP tìm kiếm TRUE trong lookup_range, tương ứng với kết quả EXACT, và trả về giá trị từ result_range của hàng khớp.
Công thức #2: FILTER
=FILTER(B2:B, EXACT(E2, A2:A)=TRUE)
Cú pháp:
=FILTER(range, condition1, [condition2, …])
Công thức FILTER điều khiển mô tả khi kết quả EXACT bằng TRUE.
Công thức #3: INDEX-MATCH
=INDEX(B2:B, MATCH(TRUE, EXACT(E2, A2:A), 0))
Phương pháp INDEX-MATCH vẫn là lựa chọn phổ biến cho nhiều người dùng Excel khi chuyển từ VLOOKUP có phân biệt chữ hoa chữ thường. Phương pháp này cũng hoạt động trong Google Sheets.
Công thức MATCH tìm kiếm TRUE trong kết quả EXACT và trả về vị trí. Vị trí này sau đó được sử dụng làm dịch chuyển hàng trong công thức INDEX trong cột B.
Với những công thức này, bạn có thể thực hiện VLOOKUP có phân biệt chữ hoa chữ thường trong Google Sheets. Hãy thử và khám phá phương pháp nào phù hợp nhất cho công việc của bạn!
