Bài này Học Excel Online sẽ hướng dẫn cách viết hàm VLOOKUP phân biệt chữ hoa và chữ thường, hướng dẫn một vài công thức có thể tra cứu các trường hợp trong Excel và chỉ ra điểm mạnh và hạn chế của mỗi hàm.

Hầu hết mọi người dùng Excel đều biết hàm nào thực hiện tra cứu theo chiều dọc trong Excel. Đúng, hàm VLOOKUP. Tuy nhiên, rất ít người biết rằng hàm VLOOKUP trong Excel không phân biệt chữ hoa chữ thường, nghĩa là nó xử lý các ký tự thường và chữ hoa giống hệt nhau.

Dưới đây là một ví dụ nhanh cho thấy hàm VLOOKUP không có khả năng phân biệt. Giả sử, nếu bạn có “bill” vì nó xuất hiện đầu tiên trong mảng tra cứu và trả về một giá trị từ ô B1.

Tiếp theo trong bài viết này, sẽ chỉ ra cho bạn cách tạo hàm VLOOKUP phân biệt chữ hoa và chữ thường. Chúng ta cũng sẽ khám phá một số hàm khác có thể phân biệt chữ hoa chữ thường trong Excel.

Chúng ta bắt đầu với hàm VLOOKUP và SUMPRODUCT khá đơn giản, tuy nhiên có một vài hạn chế đáng kể, Sau đó, chúng ta sẽ có một cái nhìn sâu hơn về công thức INDEX/MATCH phức tạp hơn một chút, hoạt động hầu như không có lỗi sai nào trong tất cả các tình huống và trên tất cả các tập dữ liệu.

Công thức VLOOKUP phân biệt chữ hoa và chữ thường

Như bạn đã biết, công thức VLOOKUP thông thường không phân biệt được chữ hoa chữ thường. Tuy nhiên, có một cách để làm cho hàm VLOOKUP trong Excel phân biệt chữ hoa chữ thường. Bạn có thể làm nó phù hợp với từng trường hợp bằng cách thêm cột hỗ trợ vào trang tính của bạn, như minh họa trong ví dụ sau.

Giả sử bạn có “ID mặt hàng” trong cột B và bạn muốn lấy giá của mặt hàng và nhận xét được liên kết từ cột C và D. Vấn đề là ID mặt hàng bao gồm cả ký tự chữ thường và chữ hoa. Ví dụ: các giá trị trong B4 (001Tvci3u) và B5 (001Tvci3U) chỉ khác nhau ở chữ cái cuối cùng, “u” và “U” tương ứng:

Như bạn có thể đoán được, công thức VLOOKUP thông thường =VLOOKUP(“001Tvci3U”,$A$2:$C$7,2,FALSE) sẽ tìm nạp $90 được liên kết với “001Tvci3u” vì nó đứng trước “001Tvci3U’ trong mảng tra cứu. Những đây không phải là những gì bạn muốn, phải không?

Để có thể thực hiện hàm VLOOKUP phân biệt chữ hoa chữ thường trong Excel, bạn cần thêm một cột hỗ trợ và điền nó bằng công thức bên dưới (trong đó cột B là cột tra cứu):

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),””)

Công thức phân tích cú pháp giá tra cứu thành các ký tự riêng lẻ, chuyển đổi từng hàng hóa thành mã của nó (ví dụ “A” là 65 và “a” là 97), sau đó nối các mã này thành một chuỗi số duy nhất.

Sau đó, bạn sử dụng công thức VLOOKUP đơn giản phù hợp với trường hợp:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

Hai điều sau đây là điều cần thiết để công thức phân biệt chữ hoa chữ thường Vlookup của bạn hoạt động chính xác:

Cột hỗ trợ phải là cột ngoài cùng bên trái trong phạm vi tra cứu (table_array argument). Giá trị tra cứu (lookup_value argument) phải là “mã ký tự” chứ không phải là giá trị thực.

Cách sử dụng công thức CODE một cách chính xác

Công thức CODE được sao chép trên cột trợ giúp nghĩa là tất cả các giá trị tra cứu có cùng ký tự. Nếu không, bạn cần biết số tối thiểu và số tối đa và thêm càng nhiều IFERROR càng nhiều ký tự sẽ tạo sự khác biệt giữa giá trị tra cứu nhỏ nhất và lớn nhất.

Ví dụ: nếu giá trị tra cứu nhỏ nhất có 3 ký tự và lớn nhất – 5 ký tự, bạn hãy sử dụng công thức này:

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),””) & IFERROR(CODE(MID(B2,4,1)),””)

Trong hàm MID, bạn chỉ định các tham số sau:

Tham số thứ nhất (văn bản) – đây là văn bản hay tham chiếu ô có chữ các ký tự bạn muốn trích xuất. (B2 trong trường hợp này)

Tham số thứ 2 (start_num) – vị trí của ký tự đầu tiên bạn muốn trích xuất. Bạn nhập 1 vào hàm MID đầu tiên, 2 vào hàm thứ hai,…

Tham số thứ 3 (num_chars) – ghi rõ số ký tự bạn muốn trả về từ văn bản. Vì bạn luôn muốn giá trị 1, bạn nhập “1” vào tất cả các hàm.

Hạn chế của hàm VLOOKUP: Hàm VLOOKUP không phải là cách tốt nhất để thực hiện tra cứu phân biệt chữ hoa chữ thường trong Excel vì thứ nhất, nó yêu cầu thêm một cột hỗ trợ và thứ hai, nó hoạt động tốt hơn trên các tập dữ liệu đồng nhất hoặc ít nhất là khi bạn biết chính xác số ký hiệu trong các giá trị tra cứu. Nếu đây không phải là trường hợp của bạn, vui lòng thử các giải pháp khác sau đây.

Công thức LOOKUP phù hợp cho chữ hoa chữ thường

Hàm LOOKUP trong Excel rất giống hàm VLOOKUP, tuy nhiên, cú pháp của nó cho phép bạn so sánh trường hợp phù hợp mà không cần thêm cột hỗ trợ. Để làm được điều này, bạn phải sử dụng LOOKUP cùng với hàm EXACT.

Nếu chúng ta lấy dữ liệu từ ví dụ trước (không có cột hỗ trợ A với mã), công thức Lookup/ Exact sau sẽ hoạt động:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

Công thức tìm kiếm qua các ô A2:A7 để tìm giá trị phân biệt chữ hoa chữ thường chính xác trong ô F2 và trả về giá trị từ cột B trong cùng một hàng.

Cũng như hàm VLOOKUP, các hàm LOOKUP hoạt động tốt cho các giá trị số và văn bản và hình bên dưới được minh họa cụ thể:

Lưu ý: Để công thức LOOKUP hoạt động chính xác, các giá trị trong cột tra cứu phải được sắp xếp theo thứ tự tăng dần, tức là từ nhỏ nhất đến lớn nhất.

Giải thích ngắn gọn việc sử dụng hàm EXACT trong hàm trên:

Hàm EXACT trong Excel so sánh hai giá trị văn bản trong tham số thứ nhất và thứ hai và trả về TRUE nếu chúng hoàn toàn giống nhau, nếu không thì FALSE. Điều thực sự quan trọng đối với chúng ta là EXACT phân biệt chữ hoa và chữ thường.

Bây giờ, hãy phân tichs cách hoạt động của LOOKUP/ EXACT: =LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

Hàm EXACT kiểm tra giá trị của ô F2 so với tất cả các mục trong phạm vi A2:A7 và nếu thấy kết quả phù hợp có phân biệt chữ hoa chữ thường, trả về TRUE và ngược lại – FALSE.  

Vì vậy bạn đã nhập TRUE trong tham số đầu tiên (lookup_value) của hàm LOOKUP, nó sẽ lấy một giá trị tương ứng từ cột trả về (cột B trong ví dụ), chỉ khi thấy kết quả chữ hoa và chữ thường phù hợp. 

Hy vọng rằng lời giải thích trên có lý và bạn đã hiểu được ý tưởng chung ngay bây giờ. Nếu bạn làm vậy, bạn sẽ không gặp bất kỳ khó khăn nào với các chức năng khác mà chúng ta sẽ tìm hiểu tiếp, vì tất cả chúng đều dựa trên cùng một nguyên tắc.

Hạn chế của LOOKUP: yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần.

SUMPRODUCT – tra cứu trường hợp giá trị văn bản và trả về số phù hợp

Như bạn đã biết, SUMPRODUCT là một hàm Excel khác có thể thực hiện tra cứu phân biệt chữ hoa chữ thường nhưng chỉ có thể trả về giá trị số. Nếu đây không phải là trường hợp của bạn, bạn có thể bỏ qua trực tiếp INDEX MATCH cung cấp giải pháp thông thường và làm việc với tất cả các loại dữ liệu.

Đầu tiên, giải thích một cách ngắn gọn cú pháp của hàm này sẽ giúp bạn hiểu rõ hơn về hàm SUMPRODUCT phân biệt chữ hoa chữ thường:

Hàm SUMPRODUCT trong Excel có nhiều thành phần trong từng mảng cụ thể và trả về tổng sản phẩm. Cú pháp của nó là:

SUMPRODUCT(array1,array2,array3, …)

Vì chúng ta muốn phân biệt chữ hoa chữ thường khi tra cứu, chúng ta sử dụng hàm EXACT từ ví dụ trước làm một trong các số nhân:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

Như bạn đã biết, hàm EXACT so sánh giá trị trong ô F2 với tất cả các mục trong cột  và nếu tìm thấy kết quả khớp chính xác có phân biệt chữ hoa chữ thường, thì trả về TRUE, ngược lại là FALSE. Trong các phép toán, Excel coi TRUE là “1” và FALSE là :0”, do đó, hàm SUMPRODUCT nhân các số trả về tính tổng các tích.

0 không tính, bởi vì 0 luôn tạo ra “0” bất kỳ số nào mà nó nhân với. Vì vậy, chúng ta hãy xem xét kỹ hơn điều gì sẽ xảy ra khi tìm thấy kết quả khớp chính xác trong cột A và “1” được trả về. Hàm SUMPRODUCT nhân 1 với một số trong cột B trong cùng một hàng và trả về chính xác số này. Điều này là do kết quả của các phép nhân khác 0, không ảnh hưởng đến giá trị trả về theo bất kỳ cách nào.

Rất tiếc, hàm SUMPRODUCT không thể xử lý các giá trị văn bản và ngày tháng vì chúng không thể được nhân lên. Trong trường hợp này, bạn sẽ được thông báo lỗi #VALUE! thông báo lỗi như trong ô F4:

Hạn chế của hàm SUMPRODUCT: chỉ trả về giá trị số.

MATCH/ INDEX – tra cứu chữ hoa chữ thường cho tất cả các loại dữ liệu

Cuối cùng, công thức phân biệt chữ hoa chữ thường không giới hạn đã xuất hiện, hoạt động trên tất cả các tập dữ liệu.

Đây là ví dụ được vận dụng từ những ví dụ trước mà bạn đã xem qua và dễ dàng giúp bạn hiểu công thức MATCH/ INDEX phân biệt chữ hoa chữ thường.

Như bạn đã biết, sự kết hợp của INDEX và MATCH được sử dụng trong Excel như một sự thay thế linh hoạt và tiện lợi hơn làm VLOOKUP. 

Đây là những điểm chính bạn cần lưu ý:

Hàm MATCH tìm kiếm giá trị tra cứu trong một phạm vi được khoanh vùng (lookup_array) và trả về vị trí tương đối của nó trong mảng. Vị trí tương đối của giá trị tra cứu chuyển trực tiếp đến tham số row_num của hàm INDEX cho phép nó trả về giá trị từ hàng đó. Để công thức có thể phân biệt được, bạn cần thêm một hàm nữa vào tổ hợp INDEX/ MATCH. Như bạn có thể dễ dàng đoán được, bạn sử dụng hàm EXACT một lần nữa:

=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))

Tuy nhiên, trong trường hợp này, chúng ta cần một công thức mảng để nó hoạt động, vì vậy hãy nhớ nhấn tổ hợp phím Ctrl + Shift + Enter để tiến hành công thức. Nếu được thực hiện đúng, công thức sẽ được đặt trong dấu ngoặc nhọn, như thể hiện trong hình minh họa bên dưới:

Tại sao INDEX/ MATCH là giải pháp tốt nhất để vlookup phân biệt chữ hoa chữ thường

Những ưu điểm chính của việc sử dụng INDEX/MATCh là:

• Nó không yêu cầu thêm cột hỗ trợ như hàm VLOOKUP.
• Nó không yêu cầu sắp xếp một cột tra cứu như hàm LOOKUP.
• Nó hoạt động với tất cả các loại dữ liệu – số, văn bản và ngày tháng.

Công thức INDEX/ MATCH phân biệt chữ hoa chữ thường một cách dễ dàng nhất.

Giả sử, một ô trong cột trả về tương ứng với giá trị tra cứu là trống. Công thức sẽ trả về gì? Không có gì. Và bây giờ, hãy xem những gì nó thực sự trả về:

Rất tiếc, công thức trả về số 0. Có thể, đây không phải là vấn đề lớn nếu bạn chỉ làm việc với giá trị văn bản. Tuy nhiên, nếu trang tính của bạn chứa các số và một số trong đó là số 0 thì đây là một vấn đề.

Trên thực tế, tất cả các công thức tra cứu khác (VLOOKUP, LOOKUP và SUMPRODUCT) mà chúng ta đã thảo luận trước đó hoạt động theo cùng một cách. Nhưng bây giờ bạn muốn một công thức tối ưu nhất có thể, phải không?

Để làm cho công thức INDEX/ MATCH phân biệt chữ hoa chữ thường một cách chính xác nhất, bạn cần đặt nó vào hàm IF để kiểm tra xem ô trả về  có trống không và không trả về gì trong trường hợp này:

=IF(INDIRECT(“B”&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>””,INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),””)

Trong công thức trên:

“B” là cột trả về

“1+” là số biến vị trí tương đối của ô do hàm MATCH trả về. Ví dụ: mảng tra cứu trong hàm MATCH là A2:A7 có nghĩa là vị trí tương đối của ô A2 là “1”, vì đây là ô đầu tiên trong mảng. Nhưng vị trí thực của ô A2 trong cột “2”, vì vậy chúng ta thêm 1 để bù chênh lệch, cho hàm INDIRECT trả về giá trị từ ô bên phải.

Hình ảnh minh họa bên dưới chứng minh hàm INDEX/ MATCH phân biệt chữ hoa chữ thường được điều chỉnh. Công thức được viết lại cho các cột B:D để thanh công thức có thể  vừa với khung hình.

Hàm INDEX/ MATCH không trả về gì nếu ô trả về trống:

Hàm trả về “0” nếu nếu ô trả về là 0:

Nếu bạn muốn hàm INDEX/ MATCH hiển thị một số thông báo khi giá trị trả về trống, bạn có thể viết thêm nội dung giữa các dấu ngoặc kép (“”) trong ở cuối công thức, như sau:

=IF(INDIRECT(“D”&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>””,INDEX($D$2:$D$7,MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),” There is nothing to return, sorry.”)

Đây là cách tra cứu trong Excel có tính đến trường hợp văn bản. Học Excel Online cảm ơn bạn đã độc và hy vọng sẽ gặp bạn trong những blog tiếp theo vào tuần tới!