=HLOOKUP("Mar",A1:E3,2,FALSE) tìm Mar ở hàng đầu tiên của A1:E3 và trả về giá trị ở hàng thứ hai của cùng cột. Đây là VLOOKUP xoay ngang, dành cho các bảng có nhãn chạy dọc phía trên.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Mar | |||
| 6 | Sales | 4,800 |
B6 tìm Mar trong hàng 1, thấy nó ở cột D và trả về hàng 2 của cột đó: 4.800. Chọn Apr ở B5 để được 5.100, hoặc đổi số 2 trong công thức thành 3 để xem chi phí.
Cú pháp hàm HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value: giá trị cần tìm ở hàng đầu tiên của bảng.table_array: bảng dữ liệu. HLOOKUP chỉ tìm ở hàng trên cùng của nó.row_index_num: hàng cần trả về, tính hàng trên cùng là 1. Một số lớn hơn số hàng của bảng cho #REF!; số 0 cho #VALUE!.range_lookup:FALSEđể tìm khớp chính xác.TRUEhoặc bỏ trống để tìm gần đúng trên một hàng đã sắp xếp.
Phép so khớp không phân biệt chữ hoa chữ thường (mar vẫn tìm ra Mar), và với FALSE giá trị cần tìm có thể dùng ký tự đại diện * và ?. Giá trị không có ở hàng đầu tiên sẽ trả về #N/A.
Tìm gần đúng theo hàng
Với TRUE, HLOOKUP tìm tiêu đề lớn nhất nhỏ hơn hoặc bằng giá trị cần tìm. Hàng đầu tiên phải được sắp xếp tăng dần từ trái sang phải.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | 0 | 2 | 5 | 10 |
| 2 | Cost | $4.50 | $6.00 | $9.50 | $14.00 |
| 3 | |||||
| 4 | Parcel (kg) | 7 | |||
| 5 | Cost | $9.50 |
7 kg không phải là một tiêu đề. Tiêu đề lớn nhất không vượt quá nó là 5, nên B5 trả về 4,50 hoặc thành 12 để được $14,00. Bảng trong công thức là B1:E2, không phải A1:E2: nó bắt đầu từ mức cân nặng đầu tiên để nhãn văn bản ở A1 không nằm trong hàng đã sắp xếp.
XLOOKUP theo hàng ngang
Trong Excel 2021 và Microsoft 365, XLOOKUP thay thế HLOOKUP. Hàm này nhận hàng cần tìm và hàng cần trả về dưới dạng hai vùng, nên không phải đếm số thứ tự hàng, và một vùng trả về cao nhiều hàng sẽ mang về cả cột.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | Profit | 1,600 | 1,400 | 1,900 | 2,100 |
| 5 | |||||
| 6 | Month | Feb | |||
| 7 | Figures | 3,900 | |||
| 8 | 2,500 | ||||
| 9 | 1,400 |
Một công thức ở B7 trả tràn ba số liệu của Feb xuống B7:B9: 3.900, 2.500 và 1.400. Nếu B8 hoặc B9 có dữ liệu, B7 sẽ báo #SPILL!. Trang XLOOKUP trình bày các tùy chọn khác của hàm này, như thông báo khi không tìm thấy và tìm kết quả khớp cuối cùng.
Xoay bảng thay vì tra ngang: TRANSPOSE
Đôi khi cách tốt hơn là tạo một bản sao dọc của bảng. =TRANSPOSE(A1:D3) trả về chính các ô đó nhưng đổi hàng thành cột, và vẫn liên kết với bảng gốc. Khi đó VLOOKUP, FILTER và biểu đồ dùng được như bình thường.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,900 | 4,800 |
| 3 | Costs | 2,600 | 2,500 | 2,900 |
| 4 | ||||
| 5 | Month | Sales | Costs | |
| 6 | Jan | 4,200 | 2,600 | |
| 7 | Feb | 3,900 | 2,500 | |
| 8 | Mar | 4,800 | 2,900 |
A5 trả tràn một khối 4 x 3: các tháng chạy dọc bên cạnh, Sales và Costs chạy ngang phía trên. Đổi doanh số của Feb ở C2 thành 4100 và bản sao cập nhật theo. Nếu chỉ cần sao chép một lần mà không dùng công thức, hãy chọn bảng, sao chép, rồi vào Home > Paste > Paste Special và đánh dấu Transpose.
Luyện tập: chi phí của một tháng
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Apr | |||
| 6 | Costs |
Đến lượt bạn: Trong B6, dùng HLOOKUP để trả về chi phí của tháng ở B5.
Câu hỏi thường gặp
VLOOKUP và HLOOKUP khác nhau thế nào?
VLOOKUP tìm dọc xuống cột đầu tiên của bảng và trả về giá trị ở một cột bên phải. HLOOKUP tìm ngang theo hàng đầu tiên và trả về giá trị ở một hàng bên dưới. Các đối số giống nhau, chỉ khác là số thứ tự hàng thay cho số thứ tự cột.
Số thứ tự hàng trong HLOOKUP là gì?
Là số của hàng cần trả về, đếm từ hàng đầu tiên của bảng là hàng 1. Trong =HLOOKUP("Mar",A1:E3,3,FALSE), số 3 nghĩa là hàng thứ ba của A1:E3. Một số lớn hơn số hàng của bảng sẽ trả về #REF!.
XLOOKUP có thay được HLOOKUP không?
Có. XLOOKUP hoạt động theo cả hai chiều: =XLOOKUP("Mar",B1:E1,B2:E2) tìm trong một hàng và trả về từ một hàng khác. Hàm này cần Excel 2021 hoặc Microsoft 365.
Vì sao HLOOKUP trả về #N/A?
Giá trị cần tìm không có ở hàng đầu tiên của bảng: gõ sai, thừa dấu cách, số lưu dạng văn bản, hoặc giá trị nằm ở hàng khác. Khi đối số cuối là TRUE, giá trị nhỏ hơn tiêu đề đầu tiên cũng trả về #N/A.
