Các hàm tìm kiếm trong Excel, đặc biệt là hàm HLOOKUP và VLOOKUP có ứng dụng rộng rãi trong việc tìm dữ liệu nào đó từ các bảng dữ liệu theo chiều dọc và ngang.
Chữ “V” trong VLOOKUP là viết tắt của vertical nghĩa là “theo chiều dọc”, có nghĩa là bạn sử dụng VLOOKUP để tra cứu các giá trị trong bảng được sắp xếp theo chiều dọc. Nếu bạn có dữ liệu được sắp xếp theo chiều ngang, hãy sử dụng hàm HLOOKUP- “Horizontal” có nghĩa là tìm kiếm theo chiều ngang, theo hàng.
Trong bài viết này, hoctin.vn sẽ giới thiệu với các bạn hai hàm tìm kiếm trong Excel để ứng dụng tìm kiếm tên, tìm kiếm giá trị.
Mục Lục Bài Viết
1. Hàm VLOOKUP Là Gì?
Hàm VLOOKUP là hàm dò tìm, tìm kiếm giá trị trong khu vực các cột (tạm gọi là khu vực dữ liệu dò tìm) và trả về kết quả là giá trị của một ô trong một cột do người dùng lựa chọn từ khu vực dữ liệu dò tìm, khi tiêu thức tìm kiếm khớp với một giá trị trong cột đầu tiên của khu vực dữ liệu dò tìm.
1.1, Cú Pháp Hàm VLOOKUP
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])
Trong đó:
- Lookup_value: giá trị bạn muốn tìm kiếm, tra cứu.
- Table_array: nơi bạn muốn tìm Lookup_value, tạm gọi là khu vực dữ liệu dò tìm.
- Col_index_ num: số thứ tự của cột trong dải ô chứa giá trị cần trả về trong kết quả của hàm VLOOKUP.
- [Range_lookup]: phạm vi tìm kiếm
- Nếu Range_lookup = 1 hoặc TRUE: dò tìm kết quả phù hợp gần đúng nếu cột đầu tiên của Table_array đã sắp xếp theo chiều tăng dần hoặc giảm dần
- Nếu Range_lookup = 0 hoặc FALSE: dò tìm kết quả chính xác, nếu không tìm thấy sẽ hiện lỗi N/A.
1.2, Ví Dụ Minh Họa Đơn Giản Hàm Tìm Kiếm Trong Excel Theo Chiều Ngang
Ví dụ tìm kiếm Giá trị hợp đồng tối thiểu và % hoa hồng theo bảng excel quy ước của hợp đồng bảo hiểm từ tên hợp đồng hoặc tên khách hàng trong trường hợp bảng quy ước này có các trường dữ liệu dạng cột.
Khi sử dụng VLOOKUP, hãy tưởng tượng rằng mọi cột trong bảng được đánh số, bắt đầu từ bên trái. Để nhận giá trị từ một cột cụ thể, hãy cung cấp số thích hợp làm Col_index_ num: “số thứ tự của cột”. Ví dụ:
Để trả kết quả về Giá trị hợp đồng tối thiểu thì Col_index_ num dưới đây là 2
Để trả kết quả về % hoa hồng thì Col_index_ num dưới đây là 3
Trong ví dụ trên:
- Tại ô D10, để tìm kiếm Giá trị hợp đồng tối thiểu của hợp đồng có loại Sản phẩm tại ô B10, gõ công thức =VLOOKUP($B10,$B$3:$D$5,2,0)
- Tại ô E10, để tìm kiếm % hoa hồng của hợp đồng có loại Sản phẩm tại ô B10, gõ công thức =VLOOKUP($B10,$B$3:$D$5,3,0)
-
Điểm khác biệt giữa 2 ô cùng dòng này là số thứ tự cột Col_index_ num để trả về kết quả tại 2 cột khác nhau từ vùng dữ liệu dò tìm Table_array
Trong đó: - VLOOKUP: là hàm dùng để tìm kiếm các thông tin của hợp đồng theo Sản phẩm từ bảng dữ liệu có các trường dữ liệu dạng cột trong Excel
- B10: Cơ sở dò tìm dựa trên loại Sản phẩm của hợp đồng là SP03, được cố định cột trong excel bằng cách ấn 3 lần phím F4.
- $B$3:$D$5: Bảng màu xanh da trời nhạt thể hiện giới hạn dò tìm, được cố định trong excel bằng 1 lần ấn F4
-
2, 3: Thứ tự cột giá trị cần lấy, lần lượt là cột Giá trị hợp đồng tối thiểu và % hoa hồng
- 0: Khi đối số range_lookup là 0 (FALSE), nếu hàm vlookup không thể tìm thấy kết quả khớp chính xác giá trị SP03 trong cột đầu tiên của khu vực dữ liệu dò tìm (cột Sản phẩm), nó sẽ trả về một lỗi #N/A (Not available- Không có số liệu)
2. Hàm HLOOKUP Tìm Kiếm Trong Excel
Tương tự Hàm VLOOKUP: HLOOKUP là hàm dò tìm theo hàng, dò tìm một giá trị trên dòng đầu tiên của khu vực dữ liệu dò tìm, trả về giá trị tại một ô trong một hàng của khu vực dữ liệu dò tìm do người dùng quy định.
Điểm khác biệt duy nhất giữa hai làm và bảng dữ liệu dò tìm sắp xếp trường dữ liệu theo dạng cột hay hàng.
2.1, Cú Pháp Hàm HLOOKUP
=HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)
Trong đó:
- Lookup_value: giá trị bạn muốn tìm kiếm, tra cứu
- Table_array: Bảng để lấy dữ liệu- nơi bạn muốn tìm Lookup_value, tạm gọi là khu vực dữ liệu dò tìm
- Row_index_ num: số thứ tự của hàng để lấy dữ liệu
- [Range_lookup]: phạm vi tìm kiếm
- Nếu Range_lookup = 1 hoặc TRUE: dò tìm kết quả phù hợp gần đúng nếu dòng đầu tiên của Table_array đã sắp xếp theo chiều tăng dần hoặc giảm dần.
- Nếu Range_lookup = 0 hoặc FALSE: dò tìm kết quả chính xác, nếu không tìm thấy sẽ hiện lỗi N/A.
2.2, Ví Dụ Minh Họa Đơn Giản Hàm HLOOKUP- Hàm Tìm Kiếm Trong Excel Theo Chiều Dọc
2.2.1, Ví Dụ Hàm HLOOKUP Trong Bài Tập Excel Cơ Bản
Ví dụ 1 cách sử dụng hàm HLOOKUP để tìm kiếm Giá trị hợp đồng tối thiểu và % hoa hồng theo bảng excel quy ước của hợp đồng bảo hiểm từ tên hợp đồng hoặc tên khách hàng trong trường hợp bảng quy ước này có các trường dữ liệu dạng hàng.
Dữ liệu này y hệt dữ liệu cho ví dụ hàm VLOOKUP ở trên, điểm khác biệt là các trường dữ liệu quy ước % hoa hồng và giá trị hợp đồng tối thiểu của sản phẩm được sắp xếp theo hàng.
Trong ví dụ trên:
- Tại ô D8, để tìm kiếm Giá trị hợp đồng tối thiểu của hợp đồng có loại Sản phẩm tại ô B8, gõ công thức =HLOOKUP($B8,$B$2:$D$4,3,0)
- Tại ô E8, để tìm kiếm % hoa hồng của hợp đồng có loại Sản phẩm tại ô B8, gõ công thức =HLOOKUP($B8,$B$2:$D$4,2,0)
-
Điểm khác biệt giữa 2 ô cùng dòng này là số thứ tự hàng Row_index_ num để trả về kết quả tại 2 cột khác nhau từ vùng dữ liệu dò tìm Table_array.
Trong đó: - HLOOKUP: là hàm dùng để tìm kiếm các thông tin của hợp đồng theo Sản phẩm từ bảng dữ liệu có các trường dữ liệu dạng hàng trong Excel
- B8: Cơ sở dò tìm dựa trên loại Sản phẩm của hợp đồng là SP03, được cố định cột trong excel bằng cách ấn 3 lần phím F4.
-
$B$2:$D$4: Bảng màu xanh da trời nhạt thể hiện giới hạn dò tìm, được cố định trong excel bằng 1 lần ấn F4.
-
2, 3: Thứ tự cột giá trị cần lấy, lần lượt là hàng % hoa hồng và Giá trị hợp đồng tối thiểu
- 0: Khi đối số range_lookup là 0 (FALSE), nếu hàm vlookup không thể tìm thấy kết quả khớp chính xác giá trị SP03 trong hàng đầu tiên của khu vực dữ liệu dò tìm (hàng Sản phẩm), nó sẽ trả về một lỗi #N/A (Not available- Không có số liệu).
2.2.2, Ví Dụ Hàm HLOOKUP Khi Kết Hợp Hàm Excel Khác
Hàm HLOOKUP còn có thể kết hợp với hàm IF tương tự như hàm if kết hợp vlookup.
Ví dụ cách dùng hàm tìm kiếm trong Excel HLOOKUP kết hợp hàm if: vẫn là bộ dữ liệu như ví dụ hàm HLOOKUP trên, thêm trường dữ liệu Năm của hợp đồng: từ năm thứ hai trở đi, mức % hoa hồng theo từng sản phẩm bảo hiểm thấp hơn năm đầu tiên.
Do đó với mỗi hợp đồng bảo hiểm, đại lý phải kiểm tra xem hợp đồng là năm đầu tiên hay năm thứ hai trở đi để tìm mức % hoa hồng phù hợp theo sản phẩm.
Hoctin.vn đã xử lý thông tin này bằng cách kết hợp hàm HLOOKUP (tìm kiếm % hoa hồng của sản phẩm) và hàm IF:
– Nếu Hợp đồng ký năm thứ nhất thì hàm HLOOKUP trả về dòng % hoa hồng năm thứ 1 có Row_index_ num (số thứ tự hàng) là 2
– Nếu không thì trả về dòng % hoa hồng từ năm thứ hai trở đi thì số thứ tự hàng là 3 như ảnh dưới đây:
Trong ví dụ trên: Tại ô E9, để tìm kiếm % hoa hồng của hợp đồng có Sản phẩm tại ô B9, gõ công thức =IF(B9=1,HLOOKUP($C9,$C$2:$E$5,2,0),HLOOKUP($C9,$C$2:$E$5,3,0))
- Hàm IF:
- Nếu “HĐ năm thứ” B9=1 thì trả về hàm HLOOKUP($C9,$C$2:$E$5,2,0) - kết quả hàm này trả về hàng số 2 của khu vực dữ liệu dò tìm là hàng % hoa hồng năm thứ 1
-
Nếu không trả về hàm HLOOKUP($C9,$C$2:$E$5,3,0)- kết quả hàm này trả về hàng số 3 của khu vực dữ liệu dò tìm là hàng % hoa hồng từ năm thứ 2 trở đi
- Hàm HLOOKUP: Dò tìm % hoa hồng của loại sản phẩm. Vùng dữ liệu dò tìm Table_array là $C$2:$E$5, Row_index_ num phụ thuộc vào kết quả hàm IF ở trên.
Trên đây là những ví dụ bài tập excel cơ bản về hàm tìm kiếm trong Excel- hàm VLOOKUP và HLOOKUP. Chúc các áp dụng thành công và vận dụng tốt trong công việc và học tập.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm excel:
Hàm IF
Hàm AND
Hàm OR