Hướng dẫn tìm kiếm dữ liệu tương ứng từ một trong nhiều cột điều kiện

Khi sử dụng Excel, chắc hẳn ta đã quen với việc tham chiếu bằng các hàm, từ một cột khóa điều kiện để trả về dữ liệu tương ứng. Vậy trong trường hợp ngược lại thì sao? Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn tìm kiếm từ một trong nhiều cột điều kiện đồng thời trả ra giá trị đó.

Xem nhanh

Bài toán tìm kiếm từ một trong nhiều cột điều kiện khởi đầu

Từ thời còn ngồi trên ghế nhà trường, ta đã được làm quen với những bài toán sử dụng VLOOKUP, mà dạng bài cơ bản nhất như sau:

Đề bài: Sử dụng hàm VLOOKUP tìm ra Phương tiện 1 ứng với Đường thủy.

Khi đó, đơn giản ta làm như sau: =VLOOKUP(giá trị dùng để tìm kiếm, bảng tìm kiến, cột tìm kiếm, kiểu tìm kiếm)

“Lớn” hơn chút nữa, biết sử dụng các hàm đa dạng hơn, ta có tổ hợp INDEX/MATCH thay thế VLOOKUP: =INDEX(cột chứa giá trị cần tìm kiếm, MATCH(giá trị dùng để tìm kiếm, cột chứa giá trị dùng để tìm kiếm, kiểu tìm kiếm))

Bây giờ, hãy đến với bài toán tổng quát hơn: Liệt kê toàn bộ phương tiện theo mỗi loại hình.

Đây là bài toán đã có lời giải. Cụ thể bạn click vào đây hoặc đây.

Từ bài toán trên, ta sẽ đến với bài toán chính lần này:

Làm thế nào để tra cứu tổng quát ngược lại?

Cụ thể: Cho tên phương tiện bên dưới, hãy lập bảng loại hình tương ứng.

Tra cứu ngược thủ công cho từng đối tượng

INDEX/MATCH

INDEX kết hợp MATCH luôn là một trợ thủ đắc lực trong việc tra cứu. Trong trường hợp này, ta có thể sử dụng công thức với từng phương tiện như sau:

=INDEX($A$2:$A$5,MATCH(A8,$B$2:$B$5,0))

VLOOKUP và CHOOSE

Ta cũng có thể sử dụng VLOOKUP kết hợp CHOOSE như một phương pháp tra cứu ngược. Cụ thể:

=VLOOKUP(A8,CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)

Rồi, sau đó, ta làm tiếp như nào?

Tất nhiên là không thể Autofill xuống được rồi!

Trong trường hợp này, ta buộc phải nghĩ đến những hướng làm khác. Học Excel Online sẽ hướng dẫn bạn một cách làm dựa trên hàm INDEX.

Tìm kiếm từ một trong nhiều cột điều kiện và trả về giá trị đó

Thiết lập logic công thức

Công thức ta mong muốn sẽ được thể hiện như sau:

Vậy làm thế nào để “tự động” mục row_num? Hãy để ý tới 2 điều kiện sau:

1. row_num chính là dòng tương ứng với các phương tiện. Ví dụ: Ô tô ở dòng 2, Tàu thủy ở dòng 3, Tàu hỏa ở dòng 4…

2. Dữ liệu của phương tiện được lấy từ cột “Phương tiện” bên dưới và có khả năng thay đổi theo 2 chiều cột và hàng.

Trong trường hợp này ta không thể sử dụng MATCH bởi đối tượng ta cần tham chiếu tới là một mảng bao gồm cả chiều ngang và dọc.

Cụ thể, ta phải tìm được mối liên quan sau:

Từ cột điều kiện Phương tiện tham chiếu sang bảng giá trị ban đầu, sau đó tạo ra một mảng mới và nhân với số dòng, cuối cùng đưa vào row_num và trả ngược kết quả bằng INDEX.

Để thực hiện việc tham chiếu trả kết quả mảng, ta sử dụng công thức mảng
Số dòng của loại hình tương ứng với phương tiện và ngược lại. Bởi vậy ta sẽ sử dụng số dòng của bảng Loại hình sang Phương tiện.