VLOOKUP với Chỉ số Col động
Nhưng làm sao, nếu bạn có thể yêu cầu hàm VLOOKUP xem xét các tiêu đề và chỉ trả về giá trị tiêu đề phù hợp. Đây được gọi là hàm VLOOKUP hai chiều.
Ví dụ: nếu tôi có công thức aVLOOKUP cho cột dấu hiệu, thì hàm VLOOKUP sẽ tìm cột nhãn hiệu trong dữ liệu và trả về giá trị từ cột đó. Điều này sẽ giải quyết vấn đề của chúng tôi.
Hmm … Được rồi, vậy làm thế nào để làm điều đó? Bằng cách sử dụng liên kết: / lookup-Formula-excel-match-function [Hàm đối sánh] trong liên kết: / công thức-và-hàm-giới thiệu-của-vlookup-function [hàm Vlookup].
Công thức chung
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value: giá trị tra cứu trong cột đầu tiên của table_array.
Table_array: phạm vi mà bạn muốn tra cứu. Ví dụ: A2, D10.
Lookup_heading: tiêu đề bạn muốn tra cứu trong các tiêu đề của table_array.
Table_headings: Tham chiếu các tiêu đề trong mảng bảng. Ví dụ. nếu bảng là A2, D10 và các tiêu đề ở đầu mỗi cột, thì A1: D1 của nó.
Vì vậy, bây giờ chúng tôi biết những gì chúng tôi cần cho col_index động, hãy làm rõ mọi thứ với một ví dụ.
Ví dụ về VLOOKUP động Với ví dụ này, chúng ta có bảng này chứa dữ liệu của các sinh viên trong phạm vi A4: E16.
Sử dụng cuộn không và đầu đề, tôi muốn truy xuất dữ liệu từ bảng này. Đối với trường hợp này, trong ô H4, tôi muốn lấy dữ liệu của cuộn không được ghi trong ô G4 và của tiêu đề trong H3. Nếu tôi thay đổi tiêu đề, dữ liệu từ phạm vi tương ứng sẽ được truy xuất trong ô H4.
Viết công thức này trong ô H4
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Vì mảng bảng của chúng ta là B4: E16, mảng tiêu đề của chúng ta trở thành B3: E3.
Lưu ý: Nếu dữ liệu của bạn có cấu trúc tốt, thì tiêu đề cột sẽ có cùng số cột và nó là hàng đầu tiên trong bảng.
Cách thức hoạt động:
Vì vậy, phần chính là đánh giá số chỉ mục cột tự động.
Để làm như vậy, chúng tôi đã sử dụng hàm MATCH.
MATCH (H3, B3: E3,0): Vì H3 chứa “học sinh”, MATCH sẽ trả về 2.
Nếu H3 có “Xếp hạng”, nó sẽ trả về 4, v.v. Công thức VLOOKUP cuối cùng sẽ có nó là col_index_num.
=VLOOKUP(G4,B4:E16,2,0)
Như chúng ta đã biết, liên kết: / tra cứu-công thức-excel-match-function [MATCH]
Bây giờ bất cứ khi nào bạn thay đổi tiêu đề trong H3, nếu nó nằm trong tiêu đề, công thức này sẽ trả về một giá trị từ cột tương ứng. Nếu không, bạn sẽ gặp lỗi # N / A.
VLOOKUP trong nhiều cột một cách nhanh chóng
Trong ví dụ trên, chúng tôi cần câu trả lời từ một giá trị cột. Nhưng nếu bạn muốn lấy nhiều cột cùng một lúc. Nếu bạn sao chép công thức trên, nó sẽ trả về lỗi. Chúng tôi cần thực hiện một số thay đổi nhỏ để làm cho nó có thể di động được.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Bây giờ sao chép H2 trong tất cả các ô trong phạm vi H2: J6 để điền dữ liệu.
Cách thức hoạt động:
và cột trong lookup_value cho MATCH (H $ 1).
$ G2: Điều này sẽ cho phép hàng thay đổi đối với giá trị tra cứu cho hàm VLOOKUP trong khi sao chép xuống dưới nhưng hạn chế cột thay đổi khi sao chép sang phải. Điều này sẽ làm cho hàm VLOOKUP chỉ tìm kiếm Id từ cột G với hàng tương đối.
Tương tự, H $ 1 sẽ cho phép cột thay đổi khi sao chép theo chiều ngang và hạn chế hàng khi sao chép xuống dưới.
Sử dụng Dãy đã Đặt tên
Ví dụ trên hoạt động tốt nhưng khó đọc và viết công thức này. Và điều này không phải là di động ở tất cả. Điều này có thể được đơn giản hóa bằng cách sử dụng các phạm vi được đặt tên.
Chúng tôi sẽ thực hiện một số đặt tên ở đây trước. Đối với ví dụ này, tôi đặt tên $ B $ 2: $ E $ 14: là Dữ liệu $ B $ 1: $ E $ 1: làm Tiêu đề H $ 1: Đặt tên là Tiêu đề. Làm cho các cột tương đối. Để làm như vậy, hãy chọn H1. Nhấn CTRL + F3, nhấp vào mới, trong phần Tham khảo, xóa ‘$’ khỏi mặt trước của H.
$ G2: Tương tự, đặt tên là RollNo. Lần này làm cho hàng tương đối bằng cách xóa ‘$’ ở phía trước của 2.
Bây giờ, khi bạn có tất cả tên trên trang tính, hãy viết công thức này vào bất kỳ đâu trên tệp excel. Nó sẽ nhận được câu trả lời chính xác luôn luôn.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
Hãy xem, bất kỳ ai cũng có thể đọc và hiểu nó.
Vì vậy, bằng cách sử dụng các phương pháp này, bạn có thể làm cho col_index_num động. Hãy cho tôi biết nếu điều này hữu ích trong phần bình luận bên dưới.