Mẹo
Hãy thử sử dụng các hàm XLOOKUP và XMATCH mới, những phiên bản cải tiến của các hàm được mô tả trong bài viết này. Các hàm mới này hoạt động theo bất kỳ hướng nào và trả về kết quả khớp chính xác theo mặc định, giúp sử dụng dễ dàng và thuận tiện hơn so với các hàm tiền nhiệm.
Giả sử bạn có danh sách các số địa điểm văn phòng và bạn cần phải biết các nhân viên trong mỗi văn phòng. Bảng tính rất lớn, vì vậy bạn có thể nghĩ đây là một nhiệm vụ khó khăn. Thực ra điều này khá dễ thực hiện với hàm tra cứu.
Hàm VLOOKUP và HLOOKUP , cùng với INDEX và MATCH là một số hàm hữu ích nhất trong Excel.
Lưu ý
Tính năng Trình hướng dẫn Tra cứu không còn sẵn dùng trong Excel.
Đây là ví dụ về cách sử dụng hàm VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
Trong ví dụ này, B2 là đối số thứ nhất—một thành phần dữ liệu mà hàm cần để hoạt động. Đối với VLOOKUP, tham đối đầu tiên này là giá trị mà bạn muốn tìm. Đối số này có thể là một tham chiếu ô hoặc một giá trị cố định như "smith" hoặc 21.000. Đối số thứ hai là phạm vi ô, C2-:E7, trong đó bạn cần tìm kiếm giá trị bạn muốn tìm. Đối số thứ ba là cột trong phạm vi ô có chứa giá trị mà bạn tìm kiếm.
Đối số thứ tư là tùy chọn. Nhập TRUE hoặc FALSE. Nếu bạn nhập TRUE, hoặc bỏ trống đối số, hàm sẽ trả về kết quả gần khớp với giá trị bạn đã chỉ ra trong đối số thứ nhất. Nếu bạn nhập FALSE, hàm sẽ khớp giá trị mà đối số thứ nhất cung cấp. Nói cách khác, việc bỏ trống đối số thứ tư — hoặc nhập TRUE — sẽ cho bạn tính linh hoạt cao hơn.
Ví dụ này cho bạn thấy cách thức mà hàm hoạt động. Khi bạn nhập giá trị vào ô B2 (đối số thứ nhất), VLOOKUP sẽ tìm kiếm các ô trong phạm vi C2:E7 (đối số thứ 2) và trả về kết quả gần đúng nhất từ cột thứ ba trong phạm vi, cột E (đối số thứ 3).
Đối số thứ tư được bỏ trống, vì vậy hàm trả về kết quả gần đúng. Nếu không, bạn sẽ phải nhập một trong những giá trị trong cột C hoặc D để có được kết quả.
Khi bạn quen với hàm VLOOKUP, hàm HLOOKUP cũng dễ sử dụng không kém. Bạn nhập cùng những đối số như thế, nhưng nó sẽ tìm kiếm theo hàng thay vì cột.
Sử dụng hàm INDEX và MATCH thay cho hàm VLOOKUP
Có một số hạn chế nhất định khi sử dụng hàm VLOOKUP—hàm VLOOKUP chỉ có thể tra cứu một giá trị từ trái sang phải. Điều này có nghĩa là cột chứa giá trị bạn tra cứu phải luôn được đặt ở bên trái cột chứa giá trị trả về. Bây giờ, nếu bảng tính của bạn không được xây dựng theo cách này, thì đừng dùng VLOOKUP. Thay vào đó, hãy sử dụng kết hợp hàm INDEX và MATCH.
Ví dụ này hiển thị một danh sách nhỏ mà trong đó giá trị Chicago mà chúng ta muốn tìm kiếm không nằm trong cột ngoài cùng bên trái. Vì vậy, chúng ta không thể sử dụng hàm VLOOKUP. Thay vào đó, chúng ta sẽ dùng hàm MATCH để tìm Chicago trong phạm vi B1:B11. Nút này được tìm thấy ở hàng 4. Sau đó, hàm INDEX dùng giá trị đó làm đối số tra cứu và tìm tập hợp cho Chicago trong cột thứ 4 (cột D). Công thức được dùng được hiển thị trong ô A14.
Bạn hãy thử đi
Nếu bạn muốn thử nghiệm với các hàm tra cứu trước khi thử sử dụng với dữ liệu của mình, dưới đây là một số dữ liệu mẫu.
Ví dụ về VLOOKUP trong thực tế
Sao chép dữ liệu sau vào một bảng tính trống.
Mẹo
Trước khi dán dữ liệu vào Excel, bạn hãy đặt độ rộng cột cho các cột từ A đến C thành 250 điểm ảnh và bấm Ngắt dòng (tab Trang đầu , nhóm Căn chỉnh ).
| Mật độ | Độ nhớt | Nhiệt độ |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Công thức | Mô tả | Kết quả |
| =VLOOKUP(1,A2:C10,2) | Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột B trong cùng hàng. | 2,17 |
| =VLOOKUP(1,A2:C10,3,TRUE) | Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột C trong cùng hàng. | 100 |
| =VLOOKUP(0,7,A2:C10,3,FALSE) | Dùng khớp chính xác để tìm giá trị 0,7 trong cột A. Vì không có sự khớp chính xác trong cột A, lỗi được trả về. | #N/A |
| =VLOOKUP(0,1,A2:C10,2,TRUE) | Dùng khớp gần đúng để tìm giá trị 0,1 trong cột A. Vì 0,1 nhỏ hơn giá trị nhỏ nhất trong cột A, lỗi được trả về. | #N/A |
| =VLOOKUP(2,A2:C10,2,TRUE) | Dùng khớp gần đúng để tìm kiếm giá trị 2 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 2 trong cột A là 1,29 và sau đó trả về giá trị từ cột B trong cùng hàng. | 1,71 |
Ví dụ về HLOOKUP
Sao chép tất cả các ô trong bảng này và dán chúng vào ô A1 trên trang tính trống trong Excel.
Mẹo
Trước khi dán dữ liệu vào Excel, bạn hãy đặt độ rộng cột cho các cột từ A đến C thành 250 điểm ảnh và bấm Ngắt dòng (tab Trang đầu , nhóm Căn chỉnh ).
| 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" |
Các ví dụ về INDEX và MATCH
Ví dụ cuối cùng này sử dụng các hàm INDEX và MATCH cùng với nhau để trả về số hóa đơn cũ nhất và ngày tương ứng cho mỗi thành phố trong số năm thành phố. Do ngày được trả về dưới dạng chữ số, chúng tôi sử dụng hàm TEXT để định dạng nó dưới dạng ngày tháng. Hàm INDEX thực sự sử dụng kết quả của hàm MATCH để làm đối số. Việc kết hợp các hàm INDEX và MATCH được sử dụng hai lần trong mỗi công thức – lần đầu để trả về số hóa đơn rồi sau đó để trả về ngày tháng.
Sao chép tất cả các ô trong bảng này và dán chúng vào ô A1 trên trang tính trống trong Excel.
Mẹo
Trước khi dán dữ liệu vào Excel, bạn hãy đặt độ rộng cột cho các cột từ A đến D thành 250 điểm ảnh và bấm Ngắt dòng (tab Trang đầu , nhóm Căn chỉnh ).
| Hóa đơn | Thành phố | Ngày lập Hóa đơn | Hóa đơn cũ nhất theo thành phố kèm theo ngày |
|---|---|---|---|
| 3115 | Atlanta | 07/04/12 | ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"d/m/yy") |
| 3137 | Atlanta | 09/04/12 | ="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"d/m/yy") |
| 3154 | Atlanta | 11/04/12 | ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"d/m/yy") |
| 3191 | Atlanta | 21/04/12 | ="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"d/m/yy") |
| 3293 | Atlanta | 25/04/12 | ="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"d/m/yy") |
| 3331 | Atlanta | 27/04/12 | |
| 3350 | Atlanta | 28/04/12 | |
| 3390 | Atlanta | 01/05/12 | |
| 3441 | Atlanta | 02/05/12 | |
| 3517 | Atlanta | 08/05/12 | |
| 3124 | Austin | 09/04/12 | |
| 3155 | Austin | 11/04/12 | |
| 3177 | Austin | 19/04/12 | |
| 3357 | Austin | 28/04/12 | |
| 3492 | Austin | 06/05/12 | |
| 3316 | Dallas | 25/04/12 | |
| 3346 | Dallas | 28/04/12 | |
| 3372 | Dallas | 01/05/12 | |
| 3414 | Dallas | 01/05/12 | |
| 3451 | Dallas | 02/05/12 | |
| 3467 | Dallas | 02/05/12 | |
| 3474 | Dallas | 04/05/12 | |
| 3490 | Dallas | 05/05/12 | |
| 3503 | Dallas | 08/05/12 | |
| 3151 | New Orleans | 09/04/12 | |
| 3438 | New Orleans | 02/05/12 | |
| 3471 | New Orleans | 04/05/12 | |
| 3160 | Tampa | 18/04/12 | |
| 3328 | Tampa | 26/04/12 | |
| 3368 | Tampa | 29/04/12 | |
| 3420 | Tampa | 01/05/12 | |
| 3501 | Tampa | 06/05/12 |
Xem Thêm
Thẻ Tham chiếu Nhanh: Ôn lại về VLOOKUP
Hàm tra cứu và tham chiếu (tham chiếu)