Mẹo: Hãy demo sử dụng những hàm Xlookup cùng xmatch mới, những phiên bản được cải tiến của những hàm được diễn đạt trong nội dung bài viết này. Các hàm mới chuyển động trong ngẫu nhiên hướng nào cùng trả về các công dụng khớp đúng mực theo mặc định, giúp dễ ợt hơn và tiện lợi hơn khi thực hiện hơn người dùng của họ.
Bạn đang xem: Hàm tìm kiếm giá trị trong excel
Giả sử bạn có một danh sách những số địa chỉ Office và bạn nên biết những nhân viên cấp dưới nào đã ở trong mỗi Office. Bảng tính là cực kỳ lớn, vì chưng vậy bạn có thể nghĩ rằng kia là nhiệm vụ đầy thách thức. Thật dễ ợt để triển khai với hàm Lookup.
Hàm vlookup với hlookup , cùng với chỉ mục với khớp,là một vài trong những các hàm hữu dụng nhất trong Excel.
Dưới đấy là ví dụ về phong thái sử dụng VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
Trong lấy ví dụ này, B2 là đối sốđầu tiên — 1 phần tử tài liệu mà hàm bắt buộc hoạt động. Đối với hàm VLOOKUP, đối số thứ nhất là cực hiếm mà bạn muốn tìm. Đối số này có thể là tham chiếu ô hoặc một giá chỉ trị thắt chặt và cố định chẳng hạn như "Smith" hoặc 21.000. Đối số máy hai là phạm vi ô, C2-: E7, trong đó để tìm kiếm kiếm giá trị mà bạn muốn tìm. Đối số thứ cha là cột vào phạm vi ô bao gồm chứa giá trị mà bạn tìm kiếm.
Đối số thứ bốn là tùy chọn. Nhập TRUE hoặc FALSE. Nếu khách hàng nhập TRUE, hoặc quăng quật trống đối số, hàm sẽ trả về hiệu quả gần khớp với giá trị chúng ta đã chỉ ra rằng trong đối số lắp thêm nhất. Nếu như khách hàng nhập FALSE, hàm vẫn khớp với giá trị vì chưng đối số đầu tiên cung cấp. Nói cách khác, hãy để trống đối số thứ tư — hoặc nhập TRUE — cung cấp cho bạn tính linh hoạt hơn.
Ví dụ này cho mình thấy phương thức mà hàm hoạt động. Khi chúng ta nhập một quý giá trong ô B2 (đối số đầu tiên), VLOOKUP đã tìm kiếm những ô trong phạm vi C2: E7 (đối số thứ hai) với trả về kết quả gần đúng duy nhất từ cột thứ bố trong phạm vi, cột E (đối số sản phẩm 3).
Đối số thứ tứ là trống, bởi vì vậy hàm đang trả về một kết quả cân xứng gần đúng. Ví như không, bạn sẽ phải nhập trong những giá trị vào cột C hoặc D để sở hữu được kết quả.
Khi chúng ta cảm thấy dễ chịu với hàm VLOOKUP, hàm HLOOKUP đầy đủ dễ sử dụng. Bạn nhập những đối số tương tự nhưng tìm kiếm kiếm trong sản phẩm thay bởi vì cột.
Sử dụng chỉ mục và khớp thay vì VLOOKUP
Có một trong những giới hạn nhất định với việc sử dụng VLOOKUP — hàm VLOOKUP chỉ rất có thể tra cứu một cực hiếm từ trái quý phái phải. Điều này có nghĩa là cột có chứa quý giá mà các bạn nhìn lên luôn ở phía bên trái cột có chứa giá trị trả về. Hiện thời nếu bảng tính của bạn không được xây dựng theo phong cách này, thì không áp dụng VLOOKUP. Sử dụng phối hợp các hàm INDEX và MATCH cầm vào đó.
Ví dụ này hiển thị một danh sách nhỏ dại mà quý hiếm mà công ty chúng tôi muốn tìm kiếm, Chicago không tồn tại trong cột ko kể cùng bên trái. Bởi vì vậy, chúng tôi không thể áp dụng VLOOKUP. Rứa vào đó, shop chúng tôi sẽ sử dụng hàm MATCH nhằm tìm Chicago trong phạm vi B1: B11. Nó được tìm thấy trong mặt hàng 4. Sau đó, chỉ mục sử dụng giá trị đó là đối số tra cứu và tìm thấy dân số cho Chicago trong cột 4th (cột D). Cách làm được áp dụng được hiển thị vào ô A14.
Để hiểu biết thêm ví dụ về kiểu cách sử dụng chỉ mục và hiệu quả thay vày VLOOKUP, hãy xem bài viết https://www.mrexcel.com/Excel-Tips/Excel-vlookup-index-match/ theo Bill Jelen, baoveangiang.com MVP.
Bạn hãy thử đi
Nếu bạn muốn thử nghiệm với những hàm tra cứu vãn trước khi trải nghiệm với dữ liệu của riêng rẽ bạn, đâ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 vào một trong những bảng tính trống.
Mẹo: Trước khi bạn dán tài liệu vào Excel, hãy để độ rộng cột cho các cột tự A mang lại điểm ảnh C mang lại 250 và bấm ngắt chiếc văn bản (tabtrang đầu, đội 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ỏ rộng hoặc bằng một trong cột A là 0,946 và sau đó trả về giá trị từ cột B vào cùng hàng. Xem thêm: Game Vua Đầu Bếp - Nhà Hàng Điên Cuồ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 một trong cột A là 0,946 và sau đó trả về giá trị từ cột C vào 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 vào cột A. Vì ko có sự khớp chính xác vào 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 vào 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ỏ rộng 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ụ HLOOKUP
Sao chép tất cả các ô vào bảng này và dán nó vào ô A1 trên trang tính trống trong Excel.
Mẹo: Trước khi chúng ta dán dữ liệu vào Excel, hãy để độ rộng lớn cột cho những cột từ bỏ A mang lại điểm ảnh C đến 250 với bấm ngắt mẫu văn bản (tabtrang đầ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ì ko tìm thấy kết quả khớp chính xác với "B", do đó giá trị lớn nhất vào 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 cha 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ó cha 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" vào hàng 2 và vào cùng cột đó là 3, trả về "c" . | "c" |
Ví dụ về chỉ mục với khớp
Ví dụ sau cùng này sử dụng các hàm INDEX với MATCH với nhau để trả về số hóa đơn nhanh nhất có thể và ngày tương ứng của nó cho mỗi năm thành phố. Do ngày được trả về dưới dạng số, công ty chúng tôi dùng hàm TEXT để định dạng nó là một ngày. Hàm INDEX đích thực sử dụng kết quả của hàm MATCH để gia công đối số. Việc kết hợp các hàm INDEX cùng MATCH được áp dụng hai lần trong những công thức – thứ 1 để trả về số hóa đối chọi rồi tiếp đến để trả về ngày tháng.
Sao chép toàn bộ các ô vào bảng này và dán chúng vào ô A1 bên trên trang tính trống trong Excel.
Mẹo: Trước khi chúng ta dán dữ liệu vào Excel, hãy đặt độ rộng cột cho những cột trường đoản cú A cho D mang lại 250 điểm ảnh và bấm ngắt dòng văn bản (tabtrang đầu, đội căn chỉnh ).
| Hóa đơn | Thành phố | Ngày lập Hóa đơn | Hóa đối kháng cũ độc nhất theo thành phố kèm theo ngày |
| 3115 | Atlanta | ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " và TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"d/m/yy") | |
| 3137 | Atlanta | ="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 | ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Ngày lập hóa đơn: " và TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"d/m/yy") | |
| 3191 | Atlanta | ="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 | ="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") Bài Viết Mới Nhất |
