Cách tham chiếu ngược bằng những hàm vô cùng đơn giản trên Excel

Chúng ta đã quá quen với việc sử dụng hàm VLOOKUP để tham chiếu dữ liệu trong Excel. Không thể phủ nhận rằng VLOOKUP đã đem đến rất nhiều lợi ích cho người sử dụng bởi sự tiện lợi, thế nhưng nhược điểm lớn nhất của VLOOKUP là chỉ tham chiếu từ trái qua phải. Để giải quyết vấn đề này, Gitiho.com sẽ giúp các bạn 3 cách để tham chiếu ngược, tức từ phải qua trái trong bảng dữ liệu Excel.

Một số ví dụ về tham chiếu ngược

Thông thường chúng ta sẽ thấy việc tham chiếu xuôi vô cùng đơn giản. Ví dụ minh hoạ sau đây sẽ giúp bạn hiểu hơn:

Yêu cầu: Tìm địa chỉ dựa vào Code ở ô A1.

Với tham chiếu thông thường thế này với cột địa chỉ nằm bên phải cột Code thì ta chỉ cần dùng VLOOKUP:

E3=VLOOKUP(E1,$A$2:$B$9,2,0)

Nhưng trong trường hợp ta tìm code tương ứng với một địa chỉ cụ thể mà giữ nguyên cấu trúc A1:B9 thì buộc chúng ta phải dùng đến tham chiếu ngược.

 

Chúng ta sẽ cùng tìm hiểu cách thức tham chiếu ngược bằng những loại hàm sau đây.

Sử dụng hàm LOOKUP để tham chiếu ngược.

Với sự hữu dụng của cả VLOOKUP và HLOOKUP nên LOOKUP có phần lép vế trước hai người đồng nghiệp. Nhưng trong trường hợp cần tham chiếu đa dạng, tham chiếu ngược thì LOOKUP lại phát huy tác dụng của nó.

- Đầu tiên ta xét những giá trị thuộc vùng B2:B9 với giá trị tại ô A1. Vậy với phép so sánh B2:B9=E1 thì sẽ trả về kết quả TRUE/FALSE. Tiếp đến, ta lấy biểu thức trên chia cho một số bất kỳ, ta nhận được TRUE=1 và FALSE=#DIV/0. Cách làm này sẽ loại bỏ các kết quả lỗi chia cho 0 thì không tính, chỉ tính khi giá trị đúng là 1

- Tiếp tới ta tìm kiếm các giá trí >=1 trong kết quả của phép so sánh 1/(B2:B9=E1)

- Giá trị trả về sẽ tương ứng với vùng A2:A9

Ta có hàm LOOKUP như sau.

Lưu ý: Với cách thức trên, việc chúng ta lấy 1 chia cho biểu thức so sánh là bởi hàm LOOKUP yêu cầu bảng dữ liệu phải được sắp xếp đúng theo trình tự. Vậy nên trong trường hợp mà bảng tính xếp lộn xộn, không theo trình tự thì ta phải đưa kết quả cần tìm về một con số, các giá trị không cần đến sẽ tính là một lỗi (như trong ví dụ này là lỗi chia cho 0). Vì thế mà hàm sẽ luôn lấy kết quả là giá trị tồn tại mà không phải lỗi.

Xem thêm: Cách dùng hàm VLOOKUP trong Excel lọc dữ liệu trùng nhau

Dùng hàm INDEX+MATCH để tham chiếu ngược

Như chúng ta đã biết, khi kết hợp hàm INDEX và hàm MATCH ta sẽ có phương thức tham chiếu dạng ma trận, tức là tạo nên các giao điểm giữa dòng và cột.

Chúng ta hãy áp dụng ngay INDEX+MATCH vào ví dụ sau đây.

Ta sẽ tra cứu vị trí của giá trị của ô E1 thuộc vùng B2:B9, sau đó kết quả đưa về sẽ là số dòng. Tiếp tới ta kết hợp với hàm INDEX để tìm ra vùng A2:A9 để ra cột cần tìm kết quả.

Vị trí kết quả cần tìm ở đây là giao điểm giữa dòng(được tìm bởi hàm MATCH)và cột (được quy định bởi hàm INDEX).

Tham khảo: Cách dùng XLOOKUP thay thế VLOOKUP để tham chiếu ngược

Kết hợp VLOOKUP và hàm CHOOSE để tham chiếu ngược

Bình thường VLOOKUP để tham chiếu xuôi từ trái qua phải, để tham chiếu ngược thì ta cần phải kết hợp VLOOKUP với hàm CHOOSE. Cách thực hiện sẽ như sau

Nhiều bạn thắc mắc rằng tại sao có thể đảo ngược cách hoạt động của VLOOKUP như vậy. Về cơ bản thì đây đúng là một cách đảo ngược của VLOOKUP, nhưng với sự trợ giúp của hàm CHOOSE thì nó sẽ "đảo ngược" lại bảng dữ liệu Excel để đưa về dạng cột chứa giá trị tìm kiếm nằm ở vị trí đầu tiên.

Hàm số Table Array trong VLOOKUP sẽ là:

 CHOOSE({1,2},$B$2:$B$9,$A$2:$A$9)

Trong đó: 

- Cột thứ nhất là vùng B2:B9

- Cột thứ 2 là vùng A2:A9

Từ đây hàm CHOOSE sẽ tạo nên 2 cột trên một bảng dữ liệu mới, hai cột này gồm một cột Địa chỉ, một cột là Code

Xem thêm: Hướng dẫn giải bài tập Excel có dùng hàmVLOOKUP, hàm IF, hàm SUM

Kết luận

Qua bài viết này, Gitiho.com mong rằng trong quá trình làm việc, bạn đọc sẽ không còn bỡ ngỡ nếu như gặp yêu cầu phải tham chiếu từ phải qua trái nữa.