Sử dụng VLOOKUP - hàm tìm giá trị trong khoảng a-b cực chuẩn

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 hàm tìm giá trị trong khoảng a-b này qua bài viết dưới đây nhé.

Hàm VLOOKUP dùng để tìm kiếm giá trị, và 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 giá trị trong khoảng 2 giá trị hoặc nhiều hơn hay còn gọi là tìm kiếm không chính xác.

VLOOKUP - Hàm tìm giá trị trong khoảng a-b là gì?

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 đó ta 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.

Để hiểu hơn về ý nghĩa các hàm và cách viết công thức trong Excel, mời bạn tham khảo khóa học sau của Gitiho:

Ví dụ về hàm tìm giá trị trong khoảng a-b

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 dựa vào bảng dữ liệu trên.

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%

Sử dụng hàm VLOOKUP để tìm kiếm giá trị chính xác:

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 khi dùng VLOOKUP 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. 

Sử dụng hàm tìm giá trị trong khoảng a-b với VLOOKUP

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)

Trong đó, 1 là tham số range_lookup, cho biết liệu bạn muốn tìm kiếm xấp xỉ giá trị hay không. Nếu bạn đặt nó là 1, nó sẽ tìm kiếm giá trị gần nhất nếu không tìm thấy giá trị chính xác.

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:

Để hiểu tại sao hàm VLOOKUP có thể tìm kiếm theo cách này, 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 VLOOKUP là hàm tìm giá trị trong khoảng a-b để á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 ở mục Blog của Gitiho nhé.

Ngoài ra để nâng cao kiến thức và cải thiện kĩ năng sử dụng Excel, bạn có thể tham khảo khóa học Tuyệt đỉnh Excel của Gitiho giúp bạn làm chủ công cụ Excel từ cơ bản đến nâng cao chỉ trong 16 giờ học. Khóa học hiện đang được giảm giá 37%, hãy nhanh tay đăng ký nhé.

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