Cách dùng hàm VLOOKUP để tìm kiếm dữ liệu theo khoảng

Chúng ta đã quen sử dụng hàm VLOOKUP để tìm kiếm chính xác tuyệt đối. Vậy còn tìm kiếm tương đối hay còn gọi là tìm kiếm theo khoảng thì sao? Hãy cùng chúng mình tìm hiểu qua bài viết dưới đây nhé.

Tuyệt đỉnh Excel: Trở thành bậc thầy Excel sau 16 giờ

Hàm VLOOKUP là hàm dò tìm, tham chiếu thông dụng nhất trong Excel. Việc sử dụng hàm VLOOKUP để tìm kiếm chính xác thì đã rất quen thuộc với các bạn nên chúng mình sẽ hướng dẫn cách dùng hàm này cho trường hợp tìm kiếm không chính xác hay còn gọi là tìm kiếm theo khoảng.

Cách dùng hàm VLOOKUP để tìm kiếm dữ liệu theo khoảng

Lý thuyết

Khi giá trị tìm kiếm của hàm VLOOKUP là dữ liệu dạng Text thì sẽ luôn là tìm kiếm chính xác; còn nếu giá trị tìm kiếm là dữ liệu dạng Number thì có thể xét tìm kiếm không chính xác. Khi giá trị tìm kiếm là Number và nó nằm trong một khoảng thì phải sử dụng phương pháp tìm kiếm không chính xác hay còn gọi là tìm kiếm chính xác tương đối. Do đó, để ra được kết quả thì phải sử dụng range_lookup = 1.

Bài tập ví dụ

Cho bảng dữ liệu về thông tin nhân viên và doanh số như sau:

Ngoài ra chúng ta có thêm một bảng phụ lục về mức doanh số và mức thưởng tương ứng:

Yêu cầu: Tính mức thưởng dựa theo doanh số của từng người. 

Xem thêm: Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí

Cách thực hiện:

Phân tích bảng phụ lục thì các bạn có thể rút ra được các điều sau:

• Doanh số từ 0 - 200 thì mức thưởng là 0%
• Doanh số từ 200 - 500 thì mức thưởng là 2%
• Doanh số từ 500-1000 thì mức thưởng là 3%
• Doanh số từ 1000 trở lên thì mức thưởng là 5%

Bây giờ chúng mình sẽ sử dụng phương thức tìm kiếm chính xác bằng hàm VLOOKUP để các bạn thấy nó không mang lại hiệu quả tính toán đúng như mong muốn trong trường hợp này. Tại ô đầu tiên của cột mức thưởng thì các bạn nhập công thức như sau:

= VLOOKUP (E30,$D$38:$E$41,2,0)

Các thành phần trong công thức này bao gồm:

• E30: Ô chứa doanh số của người đầu tiên trong bảng
• D38:E41: Vùng dữ liệu chứa thông tin về mức thưởng và mức doanh số
• col_index_num bằng 2: Lấy cột thứ 2 vì đếm trong bảng phụ lục thì cột mức thưởng là cột thứ 2.
• range_lookup bằng 0: Là phương thức tìm kiếm chính xác

Kết quả trả về là lỗi #N/A bởi vì số trong doanh số của người tên là Long không xuất hiện trong bảng phụ lục. Mặc dù nếu xét đúng theo chúng ta phân tích phía trên thì người này đạt mức doanh số nằm trong khoảng 500-1000 nên phải được thưởng 3% mới đúng.

Nhưng nếu sử dụng phương thức tìm kiếm tương đối với hàm VLOOKUP thì sẽ ra được kết quả như chúng ta mong muốn. Công thức cụ thể được sử dụng trong trường hợp này là:

= VLOOKUP (E30,$D$38:$E$41,2,1)

Sau đó khi áp dụng công thức cho ô đầu tiên của cột mức thưởng rồi thì các bạn copy công thức xuống hết bảng là sẽ thu được kết quả như sau:

Tại sao hàm VLOOKUP có thể tìm kiếm theo cách này thì cách bạn có thể đọc phần nguyên tắc tìm kiếm sau đây:

Hàm VLOOKUP sẽ tìm theo số nhỏ hơn gần nhất với lookup_value nên ta có:

• Nhỏ hơn và gần nhất so với 750 là 0,05, ứng với 3%.
• Nhỏ hơn và gần nhất so với 1250 là 0,05, ứng với 5%.
• Nhỏ hơn và gần nhất so với 340 là 0,05, ứng với 2%.
• Nhỏ hơn và gần nhất so với 180 là 0,05, ứng với 0%.
• Nhỏ hơn và gần nhất so với 600 là 0,05, ứng với 3%.
• Nhỏ hơn và gần nhất so với 490 là 0,05, ứng với 2%.

Chú ý: Khi chúng ta sử dụng hàm VLOOKUP để tìm kiếm tương đối trong trường hợp này thì bảng mức doanh số và mức thưởng phải sắp xếp các giá trị theo thứ tự từ nhỏ đến lớn.

Xem thêm: Hướng dẫn sử dụng hàm VLOOKUP để tìm và tính đơn giá trong Excel

Kết luận

Qua bài viết này, các bạn đã biết thêm được một ứng dụng của hàm VLOOKUP để áp dụng vào công việc. Chúng mình cũng đã chia sẻ rất nhiều thủ thuật với hàm VLOOKUP và các hàm khác trong Excel. Các bạn vào mục Blog của Gitiho để tham khảo nhé.

 Chúc các bạn học tập hiệu quả nhé!