Công thức Excel: Tra cứu bên trái với hàm VLOOKUP
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 CHỌN chức năng để 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à phạm vi được đặt tên.

Giải trình

Một trong những Hàm VLOOKUP của hạn chế chính 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ư thế này:

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 đưa ra lựa chọn mảng hằng số cho số chỉ mục 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 cho hàm VLOOKUP dưới dạng đối số mảng bảng. Nói cách khác, CHOOSE đang gửi một bảng tra cứu như thế này tới VLOOKUP:

Bảng tra cứu được cấu trúc lại

Sử dụng giá trị tra cứu trong E5, hàm VLOOKUP xác định vị trí 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” 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ư thế này:

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:

=INDEX(rating,MATCH(E5,score,0))

Trên thực tế, đây là một ví dụ điển hình về cách INDEX và MATCH linh hoạt hơn VLOOKUP.

https://exceljet.net/formula/left-lookup-with-vlookup