Công thức Excel: Tra cứu bên trái với hàm VLOOKUP -

Công thức chung

=VLOOKUP(A1,CHOOSE((1,2),range2,range1),2,0)

Tóm lược

Để sử dụng hàm VLOOKUP để thực hiện tra cứu ở bên trái, bạn có thể sử dụng hàm CHỌN để sắp xếp lại bảng tra cứu. Trong ví dụ được hiển thị, công thức trong F5 là:

=VLOOKUP(E5,CHOOSE((1,2),score,rating),2,0)

trong đó điểm (C5: C9) và xếp hạng (B5: B9) là các phạm vi được đặt tên.

Giải trình

Một trong những hạn chế chính của hàm VLOOKUP là nó chỉ có thể tra cứu các giá trị ở bên phải. Nói cách khác, cột chứa các giá trị tra cứu phải nằm bên trái của các giá trị bạn muốn truy xuất bằng hàm VLOOKUP. Không có cách nào để ghi đè hành vi này vì nó được cài sẵn vào hàm. Do đó, với cấu hình bình thường, không có cách nào sử dụng hàm VLOOKUP để tra cứu xếp hạng trong cột B dựa trên điểm trong cột C.

Một cách giải quyết là tự cấu trúc lại bảng tra cứu và di chuyển cột tra cứu sang bên trái của (các) giá trị tra cứu. Đó là cách tiếp cận được thực hiện trong ví dụ này, sử dụng xếp hạng ngược của hàm CHOOSE và cho điểm như sau:

CHOOSE((1,2),score,rating)

Thông thường, CHOOSE được sử dụng với một số chỉ mục duy nhất làm đối số đầu tiên và các đối số còn lại là giá trị để chọn. Tuy nhiên, ở đây chúng tôi cho phép chọn một hằng số mảng cho số chỉ số chứa hai số: (1,2). Về cơ bản, chúng tôi yêu cầu chọn cho cả giá trị thứ nhất và thứ hai.

Các giá trị được cung cấp dưới dạng hai phạm vi được đặt tên trong ví dụ: điểm và xếp hạng. Tuy nhiên, lưu ý rằng chúng tôi đang cung cấp các phạm vi này theo thứ tự đảo ngược. Hàm CHOOSE chọn cả hai phạm vi theo thứ tự được cung cấp và trả về kết quả dưới dạng một mảng như sau:

(5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible")

CHOOSE trả về mảng này trực tiếp với hàm VLOOKUP dưới dạng đối số mảng bảng. Nói cách khác, CHOOSE đang cung cấp một bảng tra cứu như sau cho VLOOKUP:

Sử dụng giá trị tra cứu trong E5, hàm VLOOKUP định vị kết quả khớp bên trong bảng mới tạo và trả về kết quả từ cột thứ hai.

Sắp xếp lại thứ tự với hằng số mảng

Trong ví dụ được hiển thị, chúng tôi đang sắp xếp lại bảng tra cứu bằng cách đảo ngược "xếp hạng" và "điểm số" bên trong hàm đã chọn. Tuy nhiên, thay vào đó chúng ta có thể sử dụng hằng số mảng để sắp xếp lại như sau:

CHOOSE((2,1),rating,score)

Kết quả là hoàn toàn giống nhau.

Với INDEX và MATCH

Mặc dù ví dụ trên hoạt động tốt, nhưng nó không phải là lý tưởng. Đối với một điều, hầu hết người dùng bình thường sẽ không hiểu công thức hoạt động như thế nào. Một giải pháp tự nhiên hơn là INDEX và MATCH. Đây là công thức tương đương: