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

Chia sẻ bài viết này:
  • 2
  •  
  •  
  •  
  •  
  •  
  •   
  •   

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ị đó.

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.

Tạo công thức

Từ trong ra ngoài, công thức của ta như sau:

=$B$2:$E$5=A8

Sau đó ta nhấn Ctrl + Shift + Enter. Mảng của ta sẽ biểu diễn như sau (ấn F9 để hiển thị trên thanh công thức):

Tiếp theo, để chuyển TRUE về 1, FALSE về 0, ta sử dụng:

=–($B$2:$E$5=A8) hoặc =N($B$2:$E$5=A8)

Đọc thêm về hàm N tại đây

Khi đã có giá trị số, ta tiến hành nhân với vị trí dòng để ra giá trị tương ứng:

Hàm sử dụng lấy ra vị trí dòng: Hàm ROW

Đối với phép nhân mảng, ta cũng sử dụng mảng cho hàm ROW. Cụ thể: =ROW($A$2:$A$5).

Phép nhân tương ứng:

=N($B$2:$E$5=A8)*ROW($A$2:$A$5) (bấm Ctrl+Shift+Enter)

Ta có thể biểu thị kết quả dưới đây:

Vậy để có thể đưa kết quả về số ta phải làm thế nào? Đáp án là hàm SUMPRODUCThoặc hàm SUM cho mảng.

Với hàm SUMPRODUCT ta chỉ cần ấn Enter, trong khi với hàm SUM ta cần Ctrl + Shift + Enter.

Trong trường hợp bạn vẫn muốn sử dụng hàm SUM? Hãy chèn thêm một hàm INDEX chuyển công thức mảng về bình thường.

=INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0)

  • =SUM(INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0))

Cuối cùng, đặt công thức vào trong hàm INDEX ban đầu để trả về kết quả và Autofill cho những ô còn lại:

=INDEX($A$1:$A$5,SUM(INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0)))


Đọc thêm về các hàm:

Bí kíp võ lâm 1: INDEX/MATCH

Bí kíp võ lâm 2: SUMPRODUCT

4 tuyệt chiêu tra cứu trong Excel

VLOOKUP, INDEX trả về hình ảnh

 

 

 

 


Chia sẻ bài viết này:
  • 2
  •  
  •  
  •  
  •  
  •  
  •   
  •