Mẹo: Hãy thử dùng các hàm XLOOKUP vàXMATCH mới, 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 việc sử dụng chúng dễ dàng và thuận tiện hơn so với các hàm trước.
Giả sử bạn có danh sách số vị trí văn phòng và bạn cần biết những nhân viên nào đang ở trong mỗi văn phòng. Bảng tính này rất lớn, vì vậy bạn có thể nghĩ rằng đó là một nhiệm vụ khó khăn. Thực ra, bạn có thể thực hiện khá dễ dàng với một hàm tra cứu.
Các 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.
Dưới đâ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ố đầu tiên—một thành phần dữ liệu mà hàm cần hoạt động. Đối với hàm VLOOKUP, đối số đầ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, để 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 với giá trị mà đối số thứ nhất cung cấp. Nói cách khác, việc để trống đối số thứ tư—hoặc nhập TRUE—cho phép bạn linh hoạt 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), hàm VLOOKUP tìm kiếm các ô trong phạm vi C2:E7 (đối số 2nd) 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ư 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 đã cảm thấy thoải mái với hàm VLOOKUP, dễ sử dụng hàm HLOOKUP. Bạn nhập cùng các đối số, nhưng nó tìm kiếm trong hàng thay vì cột.
Sử dụng hàm INDEX và MATCH thay vì hàm VLOOKUP
Có những hạn chế nhất định khi sử dụng hàm VLOOKUP—hàm VLOOKUP chỉ có thể tra cứu giá trị từ trái sang phải. Điều này có nghĩa là cột chứa giá trị mà bạn tra cứu phải luôn nằm ở bên trái cột có 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ị mà chúng ta muốn tìm kiếm, Chicago, không nằm trong cột ngoài cùng bên trái. Vì vậy, chúng tôi không thể sử dụng hàm VLOOKUP. Thay vào đó, chúng ta sẽ sử dụng hàm MATCH để tìm Chicago trong phạm vi B1:B11. Nó được tìm thấy trong 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 sử dụng được hiển thị trong ô A14.
Để biết thêm ví dụ về cách sử dụng hàm INDEX và MATCH thay vì hàm VLOOKUP, hãy xem bài viết https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ bởi Bill Jelen, Microsoft MVP.
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 dùng thử với dữ liệu của riêng mình, dưới đây là một số dữ liệu mẫu.
Ví dụ về VLOOKUP tại nơi làm việc
Sao chép dữ liệu sau đây vào một bảng tính trống.
Mẹo: Trước khi bạn dán dữ liệu vào Excel, 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 bạn dán dữ liệu vào Excel, 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" |
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 nhau để trả về số hóa đơn sớm nhất và ngày tương ứng cho mỗi thành phố trong số năm thành phố. Vì ngày được trả về dưới dạng số, chúng tôi sử dụng hàm TEXT để định 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 bạn dán dữ liệu vào Excel, 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 |
