Tìm hiểu tất cả về các hàm tra cứu & tham chiếu của Excel, chẳng hạn như VLOOKUP, HLOOKUP, MATCH, INDEX và CHOOSE
vlookup
Hàm VLOOKUP [Tra cứu theo chiều dọc] tìm kiếm một giá trị ở cột ngoài cùng bên trái của bảng, sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định
1. Chèn hàm VLOOKUP như hình bên dưới
Giải trình. hàm VLOOKUP tìm kiếm ID [104] ở cột ngoài cùng bên trái của phạm vi $E$4. $G$7 và trả về giá trị trong cùng một hàng từ cột thứ ba [đối số thứ ba được đặt thành 3]. Đối số thứ tư được đặt thành FALSE để trả về kết quả khớp chính xác hoặc lỗi #N/A nếu không tìm thấy
2. Kéo hàm VLOOKUP ở ô B2 xuống ô B11
Ghi chú. khi chúng ta kéo hàm VLOOKUP xuống, tham chiếu tuyệt đối [$E$4. $G$7] giữ nguyên, trong khi tham chiếu tương đối [A2] thay đổi thành A3, A4, A5, v.v. Truy cập trang của chúng tôi về chức năng VLOOKUP để biết thêm thông tin và nhiều ví dụ
tra cứu
Tương tự, bạn có thể sử dụng hàm HLOOKUP [Tra cứu theo chiều ngang]
Ghi chú. nếu bạn có Excel 365 hoặc Excel 2021, bạn cũng có thể sử dụng XLOOKUP để thực hiện tra cứu theo chiều ngang
Trận đấu
Hàm MATCH trả về vị trí của một giá trị trong một phạm vi nhất định
Giải trình. Màu vàng được tìm thấy ở vị trí 3 trong phạm vi E4. E7. Đối số thứ ba là tùy chọn. Đặt đối số này thành 0 để trả về vị trí của giá trị chính xác bằng lookup_value [A2] hoặc lỗi #N/A nếu không tìm thấy. Sử dụng INDEX và MATCH trong Excel và gây ấn tượng với sếp của bạn
Mục lục
Hàm INDEX bên dưới trả về một giá trị cụ thể trong phạm vi hai chiều
Giải trình. 92 được tìm thấy tại giao điểm của hàng 3 và cột 2 trong phạm vi E4. F7
Hàm INDEX bên dưới trả về một giá trị cụ thể trong phạm vi một chiều
Giải trình. 97 được tìm thấy ở vị trí 3 trong phạm vi E4. E7. Sử dụng INDEX và MATCH trong Excel và gây ấn tượng với sếp của bạn
Các hàm này giúp bạn làm việc với các mảng dữ liệu và đặc biệt hữu ích khi bạn cần tham chiếu chéo giữa các tập dữ liệu khác nhau
Các chức năng đã được nhóm thành các danh mục để bạn có thể dễ dàng tìm thấy chức năng mình cần. Mỗi liên kết sẽ đưa bạn đến một mô tả đầy đủ về chức năng, với các ví dụ về cách sử dụng và chi tiết về các lỗi phổ biến
Khi sử dụng công thức tra cứu trong Excel [chẳng hạn như VLOOKUP, XLOOKUP hoặc INDEX/MATCH], mục đích là để tìm giá trị phù hợp và lấy giá trị đó [hoặc một giá trị tương ứng trong cùng một hàng/cột] làm kết quả
Nhưng trong một số trường hợp, thay vì nhận giá trị, bạn có thể muốn công thức trả về địa chỉ ô của giá trị
Điều này có thể đặc biệt hữu ích nếu bạn có một tập dữ liệu lớn và bạn muốn tìm ra vị trí chính xác của kết quả công thức tra cứu
Có một số hàm trong Excel được thiết kế để thực hiện chính xác điều này
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách bạn có thể tìm và trả về địa chỉ ô thay vì giá trị trong Excel bằng các công thức đơn giản
Hướng dẫn này bao gồm
- Tra cứu và trả về địa chỉ ô bằng hàm ADDRESS
- Tra cứu và trả về địa chỉ ô bằng hàm CELL
Tra cứu và trả về địa chỉ ô bằng hàm ADDRESS
Hàm ADDRESS trong Excel có nghĩa là chính xác điều này
Nó lấy số hàng và số cột và cung cấp cho bạn địa chỉ ô của ô cụ thể đó
Dưới đây là cú pháp của hàm ADDRESS
=ADDRESS[row_num, column_num, [abs_num], [a1], [sheet_text]]
ở đâu
- row_num. Số hàng của ô mà bạn muốn có địa chỉ ô
- cột_num. Số cột của ô mà bạn muốn có địa chỉ
- [abs_num]. Đối số tùy chọn nơi bạn có thể chỉ định xem muốn tham chiếu ô là tuyệt đối, tương đối hay hỗn hợp
- [a1]. Đối số tùy chọn nơi bạn có thể chỉ định xem bạn muốn tham chiếu theo kiểu R1C1 hay kiểu A1
- [tờ_văn bản]. Đối số tùy chọn nơi bạn có thể chỉ định xem bạn có muốn thêm tên trang tính cùng với địa chỉ ô hay không
Bây giờ, hãy lấy một ví dụ và xem nó hoạt động như thế nào
Giả sử có một tập dữ liệu như hình dưới đây, trong đó tôi có id Nhân viên, tên của họ và bộ phận của họ và tôi muốn biết nhanh địa chỉ ô chứa bộ phận cho id nhân viên KR256
Dưới đây là công thức sẽ làm điều này
=ADDRESS[MATCH["KR256",A1:A20,0],3]
Trong công thức trên, tôi đã sử dụng hàm MATCH để tìm ra số hàng chứa id nhân viên đã cho
Và vì bộ phận nằm trong cột C, tôi đã sử dụng 3 làm đối số thứ hai
Công thức này hoạt động rất tốt, nhưng nó có một nhược điểm – nó sẽ không hoạt động nếu bạn thêm hàng phía trên tập dữ liệu hoặc một cột ở bên trái tập dữ liệu
Điều này là do khi tôi chỉ định đối số thứ hai [số cột] là 3, nó được mã hóa cứng và sẽ không thay đổi
Trong trường hợp tôi thêm bất kỳ cột nào vào bên trái của tập dữ liệu, công thức sẽ tính 3 cột từ đầu bảng tính chứ không phải từ đầu tập dữ liệu
Vì vậy, nếu bạn có một tập dữ liệu cố định và cần một công thức đơn giản, điều này sẽ hoạt động tốt
Nhưng nếu bạn cần cái này dễ hiểu hơn, hãy sử dụng cái được đề cập trong phần tiếp theo
Tra cứu và trả về địa chỉ ô bằng hàm CELL
Mặc dù hàm ADDRESS được tạo riêng để cung cấp cho bạn tham chiếu ô của số hàng và cột đã chỉ định, nhưng có một hàm khác cũng thực hiện điều này
Nó được gọi là hàm CELL [và nó có thể cung cấp cho bạn nhiều thông tin về ô hơn hàm ADDRESS]
Dưới đây là cú pháp của hàm CELL
=CELL[info_type, [reference]]
ở đâu
- info_type. thông tin về ô bạn muốn. Đây có thể là địa chỉ, số cột, tên tệp, v.v.
- [thẩm quyền giải quyết]. Đối số tùy chọn nơi bạn có thể chỉ định tham chiếu ô mà bạn cần thông tin ô
Bây giờ, hãy xem một ví dụ nơi bạn có thể sử dụng chức năng này để tra cứu và lấy tham chiếu ô
Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn biết nhanh địa chỉ ô chứa bộ phận cho id nhân viên KR256
Dưới đây là công thức sẽ làm điều này
=CELL["address", INDEX[$A$1:$D$20,MATCH["KR256",$A$1:$A$20,0],3]]
Công thức trên khá đơn giản
Tôi đã sử dụng công thức INDEX làm đối số thứ hai để lấy bộ phận cho id nhân viên KR256
Và sau đó chỉ cần bọc nó trong hàm CELL và yêu cầu nó trả về địa chỉ ô của giá trị này mà tôi nhận được từ công thức INDEX
Bây giờ đây là bí mật về lý do tại sao nó hoạt động – công thức INDEX trả về giá trị tra cứu khi bạn cung cấp cho nó tất cả các đối số cần thiết. Nhưng đồng thời, nó cũng sẽ trả về tham chiếu ô của ô kết quả đó
Trong ví dụ của chúng tôi, công thức INDEX trả về "Sales" làm giá trị kết quả, nhưng đồng thời, bạn cũng có thể sử dụng nó để cung cấp cho bạn tham chiếu ô của giá trị đó thay vì chính giá trị đó
Thông thường, khi bạn nhập công thức INDEX vào một ô, nó sẽ trả về giá trị vì đó là điều mà nó phải làm. Nhưng trong các trường hợp bắt buộc phải có tham chiếu ô, công thức INDEX sẽ cung cấp cho bạn tham chiếu ô
Trong ví dụ này, đó chính xác là những gì nó làm
Và phần hay nhất khi sử dụng công thức này là nó không bị ràng buộc với ô đầu tiên trong trang tính. Điều này có nghĩa là bạn có thể chọn bất kỳ tập dữ liệu nào [có thể ở bất kỳ đâu trong trang tính], sử dụng công thức INDEX để tra cứu thường xuyên và nó vẫn cung cấp cho bạn địa chỉ chính xác
Và nếu bạn chèn một hàng hoặc cột bổ sung, công thức sẽ điều chỉnh tương ứng để cung cấp cho bạn địa chỉ ô chính xác
Vì vậy, đây là hai công thức đơn giản mà bạn có thể sử dụng để tra cứu và tìm và trả về địa chỉ ô thay vì giá trị trong Excel