Excel cung cấp một số hàm tìm kiếm và tham chiếu để sử dụng dữ liệu trong trang tính hiện tại và tìm kiếm các giá trị dữ liệu tương tự trong trang tính khác. Hàm Vlookup trong Excel cùng với Hlookup hay Index Match là hai hàm tham chiếu và tra cứu được ứng dụng rất nhiều. Dưới đây là cú pháp hàm cùng với ví dụ minh hoạ, TinhocMOS đưa ra để giúp bạn hiểu rõ hơn.
Hàm VLOOKUP trong Excel – Hàm tham chiếu cột
Công dụng: Trả về giá trị được tìm kiếm trong một bảng hoặc một dải ô theo cột
Cú pháp: = VLOOKUP[lookup_value,table_array,col_index_num,[range_lookup]]
Trong đó:
- Lookup_value: giá trị tham chiếu, giá trị này phải thuộc cột đầu tiên của table_array
- Table_array: bảng giá trị tham chiếu, bảng này phải để ở địa chỉ tuyệt đối [sử dụng $ phía trước]
- Col_index_num: thứ tự của cột cần lấy dữ liệu trong table_array, thứ tự này tính từ 1 cho cột ngoài cùng bên trái table_array
- Range_lookup [tuỳ chọn]: giá trị chỉ định Vlookup tìm giá trị tuyệt đối hay giá trị tương đối. Thường mặc định là TRUE [tương đương với 1: giá trị tương đối, gần đúng]. Nếu muốn tìm giá trị tuyệt đối sử dụng FALSE [tương đương với 0].
Ví dụ: Công thức ở ô E3 là =IF[C3;$D$11:$E$14;2;0] – C3: Giá trị dùng để tham chiếu là “Combo lớn” – $D$11:$E$14 [địa chỉ tuyệt đối]: Bảng phụ dùng để tham chiếu chứa cột Loại và Quà tặng kèm – 2: Cột dùng để tham chiếu là cột số 2 [Cột Quà tặng kèm] – 0: Giá trị trả về chính xác Ta được kết quả ở ô E3 là “Sách hàm Excel” tham chiếu ở cột “Quà tặng kèm” tương ứng với Loại là “Combo lớn”
Tương tự với các ô bên dưới.
Xem thêm: Nhóm hàm random trong Excel [hàm RAND và RANDBETWEEN]
Hàm HLOOKUP – Hàm tham chiếu hàng
Công dụng: Trả về giá trị được tìm kiếm trong một bảng hoặc một dải ô theo hàng
Cú pháp: = HLOOKUP[lookup_value, table_array, row_index_num, [range_lookup]]
Trong đó:
- Lookup_value: giá trị tham chiếu, giá trị này phải thuộc hàng đầu tiên của table_array
- Table_array: bảng giá trị tham chiếu, bảng này phải để ở địa chỉ tuyệt đối [ sử dụng $ phía trước]
- Row_index_num: thứ tự của hàng cần lấy dữ liệu trong table_array, thứ tự này tính từ 1 cho hàng thứ nhất trong table_array
- Range_lookup [tuỳ chọn]: giá trị chỉ định Hlookup tìm giá trị tuyệt đối hay giá trị tương đối. thường mặc định là TRUE [tương đương với 1: giá trị tương đối, gần đúng], muốn tìm giá trị tuyệt đối sử dụng FALSE [tương đương với 0].
Ví dụ: Công thức ở ô E3 là =HLOOKUP[C3;$B$14:$E$15;2;0] – C3: Giá trị dùng để tham chiếu là “Combo lớn” – $B$14:$E$15 [địa chỉ tuyệt đối]: Bảng phụ dùng để tham chiếu chứa hàng Loại và Quà tặng kèm – 2: Hàng dùng để tham chiếu là hàng số 2 [Hàng Quà tặng kèm] – 0: Giá trị trả về chính xác Ta được kết quả ở ô E3 là “Sách hàm Excel” tham chiếu ở hàng “Quà tặng kèm” tương ứng với Loại là “Combo lớn”
Tương tự với các ô bên dưới.
Xem thêm: Tính tuổi trong Excel nhanh chóng với hàm YEAR và DATEDIF
Hàm INDEX & MATCH – Kết hợp hai hàm tham chiếu
Hàm INDEX và hàm MATCH là sự kết hợp hoàn hảo để khắc phục hạn chế của VLOOKUP và HLOOKUP.
Công dụng:
- Hàm MACTH cho ra số hàng số cột của dữ liệu trong bảng.
- Hàm INDEX xuất giá trị ô dữ liệu trong bảng từ số hàng và số cột.
Cú pháp: =INDEX[array, MATCH[lookup_value, lookup_array, [match_type]], [column_num]]
Trong đó:
- Array: Một phạm vi ô hoặc một hằng số mảng. Nếu mảng chỉ chứa một hàng hoặc cột, tham đối số giá row_num hoặc column_num tương ứng là tùy chọn.
- row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị.
- column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.
- lookup_value: Giá trị mà bạn muốn so khớp trong mảng tìm kiếm.
- match_type: Kiểu khớp: Số -1, 0 hoặc 1. Đối số kiểu khớp chỉ rõ cách Excel so khớp giá trị tìm kiếm với các giá trị trong mảng tìm kiếm. Giá trị mặc định cho đối số này là 1
Xem thêm: Nhóm hàm Averageif trong Excel chắc chắn dân kế toán phải biết
Tóm lại về nhóm hàm tham chiếu trong Excel
Tìm kiếm và tham chiếu là một trong top những chức năng rất hữu ích mà Excel mang lại cho người dùng. Các hàm tìm kiếm và tham chiếu sử dụng dữ liệu trong trang tính hiện tại và tìm kiếm các giá trị dữ liệu tương tự trong cả trang tính khác. Thành thạo tính năng này sẽ là nền tảng vững chắc để bạn tiếp cận với khối kiến thức nâng cao hơn.
Ngoài ra, nếu công việc của bạn gắn liền với Tin học văn phòng nhưng vì quá bận rộn nên bạn không có thời gian tham gia các lớp học trực tiếp ở trung tâm thì bạn có thể tham khảo khóa Luyện thi MOS online, và Excel AZ từ cơ bản đến nâng cao và ứng dụng của Trung tâm tin học văn phòng MOS nhé!
Thông tin liên hệ
Trung tâm tin học văn phòng MOS – Viện Đào tạo và Tư vấn Doanh nghiệp – Đại học Ngoại Thương
Email:
Hotline: 0914 444 343
comments
Excel cho Microsoft 365 Excel cho Microsoft 365 dành cho máy Mac Excel cho web Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 Xem thêm...Ít hơn
Mẹo: Hãy thử sử dụng hàm xlookup mới, một phiên bản được cải tiến của hlookup hoạt động theo bất kỳ hướng nào và trả về các kết quả khớp chính xác theo mặc định, giúp dễ dàng hơn và thuận tiện hơn khi sử dụng hơn so với người tiền nhiệm của nó.
Bài viết này mô tả cú pháp công thức và cách dùng hàm HLOOKUP trong Microsoft Excel.
Tìm kiếm một giá trị trong hàng trên cùng của bảng hoặc mảng giá trị, sau đó trả về giá trị trong cùng cột từ hàng mà bạn chỉ định trong bảng hoặc mảng. Dùng hàm HLOOKUP khi các giá trị so sánh của bạn nằm ở một hàng nằm ngang ở trên cùng một bảng dữ liệu và bạn muốn tìm xuôi xuống một số hàng đã xác định. Dùng VLOOKUP khi các giá trị so sánh của bạn nằm trong một cột ở bên trái của dữ liệu mà bạn muốn tìm.
Chữ H trong tên hàm HLOOKUP là "Horizontal - Ngang."
HLOOKUP[lookup_value, table_array, row_index_num, [range_lookup]]
Cú pháp hàm HLOOKUP có các đối số sau đây:
-
Lookup_value Bắt buộc. Giá trị cần tìm trong hàng thứ nhất của bảng. Lookup_value có thể là một giá trị, tham chiếu hoặc chuỗi văn bản.
-
Table_array Bắt buộc. Một bảng thông tin để tìm kiếm dữ liệu trong đó. Hãy dùng tham chiếu tới một phạm vi hoặc một tên phạm vi.
-
Các giá trị trong hàng thứ nhất của table_array có thể là văn bản, số hoặc giá trị lô-gic.
-
Nếu range_lookup là TRUE, các giá trị trong hàng thứ nhất của table_array phải được đặt theo thứ tự tăng dần: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; nếu không, hàm HLOOKUP có thể đưa ra giá trị không đúng. Nếu range_lookup là FALSE, thì không cần phải sắp xếp table_array.
-
Văn bản chữ hoa và chữ thường tương đương nhau.
-
Sắp xếp các giá trị theo thứ tự tăng dần, từ trái sang phải. Để biết thêm thông tin, vui lòng xem mục Sắp xếp dữ liệu trong dải ô hoặc bảng.
-
-
Row_index_num Bắt buộc. Số hàng trong table_array có giá trị kết hợp sẽ được trả về. Row_index_num 1 trả về giá trị hàng đầu tiên trong table_array, một row_index_num của 2 trả về giá trị hàng thứ hai trong table_array, v.v. Nếu row_index_num nhỏ hơn 1, hàm HLOOKUP trả về #VALUE! giá trị lỗi; Nếu row_index_num lớn hơn số hàng trên table_array, hàm HLOOKUP trả về #REF! .
-
Range_lookup Tùy chọn. Một giá trị lô-gic cho biết bạn có muốn HLOOKUP tìm thấy một kết quả khớp chính xác hay kết quả khớp tương đối. Nếu đối số này là TRUE hoặc được bỏ qua, thì hàm sẽ trả về kết quả khớp tương đối. Nói cách khác, nếu không tìm thấy một kết quả khớp chính xác thì hàm sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn lookup_value. Nếu đối số này là FALSE, hàm HLOOKUP sẽ tìm một kết quả khớp chính xác. Nếu không tìm thấy kết quả khớp chính xác, hàm sẽ trả về giá trị lỗi #N/A.
-
Nếu hàm HLOOKUP không tìm thấy lookup_value, và range_lookup là TRUE, thì nó dùng giá trị lớn nhất nhỏ hơn lookup_value.
-
Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong hàng thứ nhất của table_array, thì hàm HLOOKUP trả về giá trị lỗi #N/A.
-
Nếu range_lookup là FALSE và lookup_value là văn bản, bạn có thể sử dụng các ký tự đại diện, dấu chấm hỏi [?] và dấu sao [*], trong lookup_value. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã [~] trước ký tự.
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
| Trục | Trụ | Bu-lông |
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Công thức | Mô tả | Kết quả |
| =HLOOKUP["Trục", A1:C4, 2, TRUE] | Tìm kiếm "Trục" ở hàng 1 và trả về giá trị từ hàng 2 trong cùng cột [Cột A]. | 4 |
| =HLOOKUP["Trụ", A1:C4, 3, FALSE] | Tìm kiếm "Trụ" ở hàng 1 và trả về giá trị từ hàng 3 trong cùng cột [Cột B]. | 7 |
| =HLOOKUP["B", A1:C4, 3, TRUE] | Tìm kiếm "B" ở hàng 1 và trả về giá trị từ hàng 3 trong cùng cột. Vì không tìm thấy kết quả khớp chính xác với "B", cho nên giá trị lớn nhất trong hàng 1 mà nhỏ hơn "B" sẽ được dùng: "Trục," trong cột A. | 5 |
| =HLOOKUP["Bu-lông", A1:C4, 4] | Tìm kiếm "Bu-lông" ở hàng 1 và trả về giá trị từ hàng 4 trong cùng cột [Cột C]. | 11 |
| =HLOOKUP[3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE] | Tìm kiếm số 3 trong hằng số mảng ba hàng và trả về giá trị từ hàng 2 trong cùng cột [trong trường hợp này là cột thứ ba]. Có ba hàng chứa giá trị trong hằng số mảng, mỗi hàng được phân cách bằng dấu chấm phẩy [;]. Vì tìm thấy "c" trong hàng 2 và trong cùng cột đó là 3, trả về "c" . | "c" |