Công thức chung
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Tóm lược
Để đảo ngược hàm VLOOKUP - tức là để tìm giá trị tra cứu ban đầu bằng cách sử dụng kết quả công thức hàm VLOOKUP - bạn có thể sử dụng một công thức phức tạp dựa trên hàm CHOOSE hoặc các công thức đơn giản hơn dựa trên INDEX và MATCH hoặc XLOOKUP như được giải thích bên dưới. Trong ví dụ được hiển thị, công thức trong H10 là:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Với thiết lập này, hàm VLOOKUP tìm tùy chọn có liên quan đến chi phí là 3000 và trả về "C".
Lưu ý: đây là một chủ đề nâng cao hơn. Nếu bạn mới bắt đầu với VLOOKUP, hãy bắt đầu tại đây.
Giới thiệu
Một 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 có giá trị tra cứu phải ở bên trái giá trị bạn muốn truy xuất bằng hàm VLOOKUP. Kết quả là, với cấu hình tiêu chuẩn, không có cách nào sử dụng VLOOKUP để "nhìn trái" và đảo ngược tra cứu ban đầu.
Từ quan điểm của hàm VLOOKUP, chúng ta có thể hình dung vấn đề như sau:
Cách giải quyết được giải thích bên dưới sử dụng hàm CHOOSE để sắp xếp lại bảng bên trong hàm VLOOKUP.
Giải trình
Bắt đầu từ đầu, công thức trong H5 là một công thức VLOOKUP bình thường:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Sử dụng G5 làm giá trị tra cứu ("C") và dữ liệu trong B5: D8 làm mảng bảng, hàm VLOOKUP thực hiện tra cứu các giá trị trong cột B và trả về giá trị tương ứng từ cột 3 (cột D), 3000. Lưu ý không (0) được cung cấp làm đối số cuối cùng để buộc kết hợp chính xác.
Công thức trong G10 chỉ đơn giản là lấy kết quả từ H5:
=H5 // 3000
Để thực hiện tra cứu ngược, công thức trong H10 là:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Một chút khó khăn là hàm CHOOSE, được sử dụng để sắp xếp lại mảng bảng sao cho Cost là cột đầu tiên và Option là cột cuối cùng:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Chức năng CHOOSE được thiết kế để chọn một giá trị dựa trên chỉ mục số. Trong trường hợp này, chúng tôi đang cung cấp ba giá trị chỉ mục trong một hằng số mảng:
(3,2,1) // array constant
Nói cách khác, chúng tôi yêu cầu cột 3, sau đó đến cột 2, rồi đến cột 1. Tiếp theo là ba phạm vi đại diện cho mỗi cột của bảng theo thứ tự chúng xuất hiện trên trang tính.
Với cấu hình này, CHOOSE trả về tất cả ba cột trong một mảng 2D như sau:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Nếu chúng ta hình dung mảng này như một bảng trên trang tính, chúng ta có:
Lưu ý: các tiêu đề không phải là một phần của mảng và chỉ được hiển thị ở đây để rõ ràng.
Một cách hiệu quả, chúng tôi đã hoán đổi cột 1 và 3. Bảng được sắp xếp lại được trả về trực tiếp hàm VLOOKUP, khớp với 3000 và trả về giá trị tương ứng từ cột 3, "C".
Với INDEX và MATCH
Giải pháp trên hoạt động tốt, nhưng rất khó để đề xuất vì hầu hết người dùng sẽ không hiểu cách hoạt động của công thức. Một giải pháp tốt hơn là INDEX và MATCH, sử dụng công thức như sau:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Ở đây, hàm MATCH tìm giá trị 3000 trong D5: D8 và trả về vị trí của nó, 3:
MATCH(G10,D5:D8,0) // returns 3
Lưu ý: MATCH được định cấu hình cho đối sánh chính xác bằng cách đặt đối số cuối cùng thành không (0).
MATCH trả về kết quả trực tiếp cho INDEX dưới dạng số hàng, vì vậy công thức sẽ trở thành:
=INDEX(B5:B8,3) // returns "C"
và INDEX trả về giá trị từ hàng thứ ba của B5: B8, "C".
Công thức này cho thấy cách INDEX và MATCH có thể linh hoạt hơn hàm VLOOKUP.
Với XLOOKUP
XLOOKUP cũng cung cấp một giải pháp rất tốt. Công thức tương đương là:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Với giá trị tra cứu từ G10 (3000), mảng lớn gồm D5: D8 (chi phí) và mảng kết quả là B5: B8 (tùy chọn), XLOOKUP định vị 3000 trong mảng tra cứu và trả về mục tương ứng từ mảng kết quả, "C". Vì XLOOKUP thực hiện đối sánh chính xác theo mặc định, nên không cần đặt chế độ đối sánh rõ ràng.
