Công thức Excel: Ví dụ về hàm VLOOKUP ngược
Công thức Excel: Ví dụ về hàm VLOOKUP ngược

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 VLOOKUP – bạn có thể sử dụng một công thức phức tạp dựa trên CHỌN chức nănghoặ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 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, bắt đầu ở đây.

Giới thiệu

Một hạn chế chính của VLOOKUP có phải nó chỉ có thể tra cứu các giá trị cho đúng. 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 hàm 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ái bàn chúng ta có so với cái bàn chúng ta cần

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 dưới dạng bảng mả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. Chú ý số không (0) được cung cấp làm đối số cuối cùng để buộc khớ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 một chỉ mục số. Trong trường hợp này, chúng tôi đang cung cấp số ba giá trị chỉ mục trong một mảng hằng số:

{3,2,1} // array constant

Nói cách khác, chúng tôi đang 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 2D duy nhất mảng như thế này:

{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ó:

Bảng được sắp xếp lại theo chức năng CHOOSE

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ông thức hoạt động như thế nào. 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 một giá trị tra cứu từ G10 (3000), almảng ookup của D5: D8 (chi phí) và a mảng kết quả của 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.

Tập tin đính kèm

https://exceljet.net/formula/reverse-vlookup-example