cách sử dụng hàm Hlookup kết hợp hàm Left, Mid, Right 2021

Trong bài viết này, Tinvanphong.com sẽ hướng dẫn cách sử dụng hàm HLOOKUP kết hợp với hàm Left, Mid, Right trong Excel. Hàm HLOOKUP, được sử dụng khi muốn tìm một giá trị nhất định theo hàng ngang bảng và trả lại một giá trị khác trong cùng cột từ hàng mà bạn chỉ định. Đôi khi việc sử dụng mỗi hàm HLOOKUP, cũng không thể xử lý triệt để bảng tính, chính vì vậy việc kết hợp với các hàm khác như Left, Mid, Right… là rất cần thiết. Chúng tôi sẽ hướng dẫn kĩ hơn trong bài viết sau đây, thông qua các ví dụ cụ thể.

Hướng dẫn cách sử dụng hàm Hlookup kết hợp hàm Left, Mid, Right

1.Hàm HLOOKUP

1.1 Công dụng:

Sử dụng hàm Hlookup, bạn sẽ dễ dàng tìm kiếm và tham chiếu theo các dòng, các cột để sau khi thỏa mãn các điều kiện đặt ra kết quả chính xác được trả về một cách nhanh chóng.

LI�N HỆ QUẢNG C�O TẠI Đ�Y : 0988 123 126

1.2 Cú pháp lệnh :

HLOOKUP(Lookup_value, Table_array, row_index_ num, Range_lookup)

Trong đó :

Lookup_value: Giá trị cần dò tìm.
Table_array (Bắt buộc): Bảng hoặc mảng tìm kiếm giá trị, gồm hai hàng dữ liệu trở lên. Có thể là mảng thường, mảng được đặt tên hoặc bảng Excel. Các giá trị tìm kiếm phải được đặt ở hàng đầu tiên của table_array và có thể là văn bản, số hoặc giá trị lô-gic.
Row_index_num (Bắt buộc): số hàng trong table_array nơi mà giá trị được trả về. Ví dụ: Row_index_num = 1 trả về giá trị hàng đầu tiên trong table_array, row_index_num = 2 trả về giá trị hàng thứ hai trong table_array,…
- Nếu row_index_num nhỏ hơn 1, hàm Hlookup trả về #VALUE! giá trị lỗi.
- Nếu row_index_num lớn hơn số hàng trên table_array, HLOOKUP trả về #REF! .
Range_lookup (Tùy chọn): Lựa chọn giá trị lô-gic cho biết bạn có muốn Hlookup trả về một kết quả có giá trị khớp chính xác hay khớp tương đối.
- Range_lookup = 1 (TRUE) hoặc được bỏ qua: hàm sẽ trả về kết quả khớp tương đối. Nói cách khác, nếu không tìm thấy một kết quả khớp chính xác thì hàm sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn lookup_value.
- Range_lookup = 0 (FALSE): hàm Hlookup sẽ tìm một kết quả khớp chính xác. Nếu không tìm thấy kết quả khớp chính xác, hàm sẽ trả về giá trị lỗi #N/A.

Ví dụ về hàm Hlookup, các bạn có thể tham khảo bài viết sau đây để hiểu rõ hơn: Cách sử dụng hàm Hlookup trong excel

2.Hàm Hlookup kết hợp với hàm Left

Như các bạn đã biết, hàm Left là hàm lấy ký tự bên trái một chuỗi bất kỳ, khi kết hợp Hlookup và Left giúp cho việc dò tìm kết quả nhanh và chính xác.

* Cú pháp hàm Left: =Left(text,n)

Chúng ta có bảng kết quả tuyển sinh như sau.

Yêu cầu :Căn cứ vào ký tự đầu của MÃ SỐ NGÀNH-ƯU TIÊN tra cứu trong bảng NGÀNH HỌC cập nhật TÊN NGÀNH vào bảng kết quả tuyển sinh.

Tại ô D4, chúng ta sẽ có công thức như sau:

=HLOOKUP(LEFT(C4,1),$B$9:$E$10,2,0)

Trong đó:

LEFT(C4,1) là giá trị dùng để dò tìm, tương ứng chữ đầu tiên bên trên trái của mã số ngành ưu tiên, giá trị dò tìm nằm ở hàng 2, bảng $B$9:$E$10 (các bạn nhấn F4 để khóa bảng). Kết quả trả về sẽ xếp đúng ngành Tin học.

Sau đó, các bạn kéo công thức cho các ô còn lại là xong.

3.Hàm Hlookup kết hợp với hàm Right

Hàm Right là hàm lấy ký tự bên phải một chuỗi bất kỳ, khi kết hợp Hlookup và Right cũng tương tự như Hlookup kết hợp Left giúp cho việc dò tìm kết quả nhanh và chính xác.

* Cú pháp hàm Right: =Right(text,n)

Chúng ta có bài tập như hình, yêu cầu của bài là tính số tiền ăn như sau:

Yêu cầu : Tiền ăn = Số ngày ở * Giá ăn. (Định dạng 1,000).

Được biết: Giá ăn: Dựa vào 2 ký tự cuối của Mã phòng, tra trong BẢNG GIÁ ĂN để có giá trị hợp lý.

Tại ô H3 chúng ta có công thức như sau:

=F3*HLOOKUP(RIGHT(C3,2), $G$12:$J$13,2,0) *1000

Trong đó:

F3 là số ngày ở.

RIGHT(C3,2) là giá trị dùng để dò tìm, tương ứng hai chữ số bên phải của Mã PH, giá trị dò tìm nằm ở hàng 2, bảng $G$12:$J$13( các bạn nhấn F4 để khóa bảng tính).

*1000 là do đề bài yêu cầu định dạng cho số tiền ăn là 1,000

Sau đó, các bạn kéo công thức cho các ô còn lại là xong.

4.Hàm Hlookup kết hợp với hàm Mid

Hàm Mid là hàm cắt chuỗi lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí mà bạn chỉ định, dựa vào số lượng ký tự do bạn chỉ định.,

Cấu trúc của hàm MID là: MID(text,start_num,num_chars)

Các giá trị trong hàm MID

text: Là chuỗi ký tự chứa các ký tự cần cắt, được đặt trong dấu ngoặc kép ” ” hoặc là tham chiếu đến 1 ô có chứa chuỗi ký tự. Đây là giá trị bắt buộc.
start_num: Bạn nhập vào giá trị số để chỉ định vị trí của ký tự đầu tiên bạn muốn trích xuất. Ví dụ vị trí của ký tự đầu tiên trong chuỗi ký tự là 1. Đây là giá trị bắt buộc.
num_chars: Chỉ định số lượng ký tự bạn muốn hàm MID trả về từ chuỗi ký tự. Đây là giá trị bắt buộc.

Bài tập ví dụ bên dưới, chúng ta sẽ kết hợp hàm Hlookup và hàm Mid để tính cột Thuế theo yêu cầu:

Yêu cầu : Tính Thuế = Thành tiền * Tỉ lệ. Biết rằng:

+Tỉ lệ: Dựa vào 2 ký tự thứ 2, 3 của Mã hàng để lấy Tỉ lệ tương ứng trong BẢNG 2.

Tại ô E3 chúng ta nhập công thức như sau:

=D3*HLOOKUP(VALUE(MID(A3,2,2)),$F$14:$H$15,2,2)

Trong đó:

D3 là thành tiền

MID(A3,2,2) chỉ giá trị dùng để dò tìm là ký tự ở giữa của mã hàng, nằm vị trí thứ 2 từ trái sang và lấy 2 ký tự. Giá trị dò tìm nằm ở hàng 2, bảng $F$14:$H$15( các bạn nhấn F4 để khóa bảng tính).

Do cột Thuế chúng ta muốn lấy là số, vì vậy mà các bạn cần phải thêm VALUE vào trước hàm Mid, công dụng của hàm VALUE là chuyển từ dạng text sang dạng số.

Như vậy, Tinvanphong.com đã hướng dẫn các bạn cách sử dụng hàm Hlookup kết hợp hàm LEFT, MID, RIGHT thông qua các ví dụ. Vậy qua cả 3 ví dụ trên, chúng ta rút ra bài học là không phải bài nào cũng cho sẵn hàm để viết, mà các bạn tự biến đổi cho phù hợp với yêu cầu của đề bài nhé. Các ban có thể tham khảo thêm các hàm excel cơ bản sau. Chúc các bạn thành công!.

Tham khảo thêm các bài viết sau.

–Hướng dẫn cách sử dụng hàm Sumifs trong excel

–Cách sử dụng hàm iserror trong excel

–Cách sử dụng hàm datevalue trong excel