Ứng dụng của hàm Hlookup trong Excel (Phần 2)

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Ứng dụng của hàm Hlookup trong Excel (Phần 2)

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Trong bài viết này, chúng tôi sẽ tập trung vào các đặc điểm của hàm Hlookup trong Excel và đưa một vài ví dụ minh hoạ giúp bạn sử dụng Hlookup một cách hiệu quả nhất.

3. Ô tham chiếu tuyệt đối và tương đối trong hàm HLookup

Nếu bạn đang viết hàm Hlookup cho một ô, bạn có thể không cần quan tâm nên sử dụng ô tham chiếu tuyệt đối hay tương đối vì cả hai đều phù hợp. Tuy nhiên, khi sao chép một công thức cho nhiều ô tham chiếu, bạn cần phân biệt được hai loại này

  • table_array luôn được đặt cố định bằng các ô tham chiếu tuyệt đối với kí hiệu “$”, ví dụ $B$1:$I$2
  • tham chiếu lookup_value là tương đối hoặc hỗn hợp dựa trên logic kinh doanh của bạn

Để dễ hiểu hơn, chúng ta cùng xem kĩ hàm Hlookup khi lấy dữ liệu từ một trang tính khác

=HLOOKUP (B$1, Duongkinh!$B$1:$I$2, 2, FALSE)

Trong công thức trên, chúng ta sử dụng tham chiếu tuyệt đối ($B$1:$I$2) trong table_array vì nó cần được giữ không đổi khi công thức được sao chép từ các ô khác

Đối với lookup_value (B$1), chúng ta sử dụng tham chiếu hỗn hợp, cột tương đối và hàng tuyệt đối vì các giá trị tìm kiếm (tên hành tinh) nằm trên cùng một hàng (hàng 1) nhưng ở các cột khác nhau (từ B đến I) và cột tham chiếu nên thay đổi dựa trên vị trí tương đối của ô chứa công thức được sao chép.

4. Index/Match, một công thức hiệu quả khác bên cạnh hàm Hlookup

Như các bạn đã biết, hàm HLOOKUP có một vài hạn chế với nhược điểm lớn nhất là không thể thể tìm các giá trị nằm ngoài hàng đầu và phải xếp các giá trị theo thứ tự khi tìm kết quả khớp tương đối. May mắn thay, sự kết hợp giữa hàm INDEX và MATCH sẽ khắc phục được những điểm này:INDEX (nơi chứa giá trị về, MATCH (giá trị tìm kiếm, nơi tìm giá trị, 0))

Giả sử giá trị tìm kiếm ở ô B7, bạn đang tìm một kết quả khớp ở hàng 2 (B2:I2), và muốn giá trị trả về từ hàng 1 (B1:I1), công thức như sau

=INDEX (B1:I1, MATCH (B7, B2:I2, 0))

Trong hình chụp dưới đây, bạn có thể thấy trong cả 2 trường hợp INDEX MATCH đều hoạt động tốt.

5. Cách sử dụng Hlookup phân biệt dạng chữ trong Excel

Như đã đề cập lúc đầu, hàm HLOOKUP không thể phân biệt chữ thường và in hoa. Trong nhiều trường hợp khi dạng chữ của kí tự quan trọng, bạn có thể dùng hàm EXACT để so sánh các ô và đặt bên trong hàm INDEX MATCH như trong ví dụ trước:

INDEX (hàng chứa giá trị trả về, MATCH (TRUE, EXACT (hàng tìm kiếm, giá trị tìm kiếm), 0))

Giả sử giá trị tìm kiếm của bạn ở ô B4, mảng tìm kiếm là B1:I1, mảng giá trị trả về là B2:I2, công thức có dạng như sau:

=INDEX (B2:I2, MATCH (TRUE, EXACT (B1:I1, B4), 0))

Cách sử dụng Hlookup phân biệt dạng chữ trong Excel

Cách sử dụng Hlookup phân biệt dạng chữ trong Excel

Lưu ý: Đây là công thức mảng do đó bạn phải ấn tổ hợp Ctrl + Shift + Enter sau khi viết công thức

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ