Hướng dẫn cách tìm giá trị nhỏ hơn gần nhất với giá trị so sánh

Khi dò tìm, tham chiếu theo các giá trị số, có thể bạn sẽ gặp phải yêu cầu tìm ra các giá trị nhỏ hơn và gần nhất với giá trị so sánh. Vậy làm cách nào để thực hiện yêu cầu này? Hãy cùng tìm hiểu cách làm trong bài viết này nhé.

Xem nhanh

Tìm giá trị nhỏ hơn gần nhất trong danh sách được sắp xếp

Ví dụ chúng ta có danh sách các số tiêu chuẩn ở cột A, từ A2:A6. Trong danh sách các sốố này được sắp xếp theo thứ tự tăng dần.

Xác định số nhỏ hơn gần nhất với giá trị số ở ô C3.

Để thực hiện yêu cầu này, chúng ta có thể sử dụng 2 hàm là LOOKUP và VLOOKUP. Cách làm cụ thể như sau:

Sử dụng hàm LOOKUP

Hàm LOOKUP là 1 hàm tìm kiếm cơ bản trong Excel, trong đó khi bảng dữ liệu chứa các giá trị cần tìm được sắp xếp theo thứ tự tăng dần thì chúng ta có thể tìm được số nhỏ hơn gần nhất với số cần tìm.

Tại ô D3 chúng ta dùng công thức như sau:

=LOOKUP(C3,$A$2:$A$6)

Trong đó:

Lookup_value = C3 là giá trị cần tìm
Lookup_vector = A2:A6 là vùng chứa các giá trị tham chiếu.

Kết quả của hàm là 200, là số nhỏ hơn 230 và gần nhất với số 230.

Sử dụng hàm VLOOKUP

Hàm VLOOKUP cũng là 1 hàm rất hay sử dụng trong Excel. Trong trường hợp này chúng ta có thể sử dụng hàm VLOOKUP như sau:

E3=VLOOKUP(C3,$A$2:$A$6,1,1)

lookup_value = C3 là giá trị cần tìm
table_array = A2:A6 là vùng cần tham chiếu
col_index_num = 1 vì chỉ có 1 cột trong vùng tham chiếu
range_lookup = 1 là tìm kiếm tương đối, kết quả là giá trị nhỏ hơn gần nhất.

Khi sử dụng hàm VLOOKUP với range_lookup = 1 (hay TRUE) thì hàm sẽ tìm kiếm theo phương pháp tương đối, kết quả trả về là giá trị nhỏ hơn gần nhất với số cần tìm.

Chỉ thực hiện được điều này khi:

Tìm kiếm theo giá trị Số
Vùng cần tham chiếu (vùng kết quả cần tìm) được sắp xếp theo thứ tự tăng dần

Tìm giá trị nhỏ hơn gần nhất trong danh sách không được sắp xếp

Hai trường hợp trên chúng ta đều thấy danh sách Tiêu chuẩn cần phải được sắp xếp theo thứ tự tăng dần thì mới làm được. Vậy nếu như danh sách này không được sắp xếp thì sao?

Để giải quyết yêu cầu này, chúng ta sử dụng 2 hàm LARGE và COUNTIF kết hợp với nhau.

Xác định thứ tự của số nhỏ hơn gần nhất với hàm COUNTIF

Để tìm được số nhỏ hơn gần nhất, chúng ta sẽ nghĩ theo hướng:

Có bao nhiêu số lớn hơn số cần tìm?
Số nhỏ hơn gần nhất là vị trí thứ mấy?

Khi đó ta có thể sử dụng hàm COUNTIF như sau:

D3=COUNTIF(A2:A6,”>”&C3)+1

Ý nghĩa của hàm này là:

COUNTIF(A2:A6,”>”&C3) đếm xem có bao nhiêu số trong vùng A2:A6 có giá trị lớn hơn giá trị ở ô C3
COUNTIF(A2:A6,”>”&C3)+1 khi đó giá trị nhỏ hơn gần nhất sẽ nằm ở vị trí tiếp theo (+1).

Trong ví dụ trên, ta thấy kết quả bằng 2, tức là:

Có 1 số trong danh sách Tiêu chuẩn lớn hơn số 230
Vị trí số nhỏ hơn gần nhất với số 230 là vị trí số lớn thứ 2 trong danh sách Tiêu chuẩn

=> Để xác định cụ thể đó là số nào, chúng ta dùng thêm hàm LARGE

Xác định giá trị số nhỏ hơn gần nhất với hàm LARGE

Hàm Large là hàm giúp tìm số lớn thứ mấy ở trong 1 dãy số.

Trong hàm COUNTIF ở trên, chúng ta đã xác định được số nhỏ hơn gần nhất nằm ở vị trí số lớn thứ mấy trong danh sách. Vì vậy sẽ rất đơn giản để áp dụng với hàm LARGE.

Cách làm như sau: