EA;m...Ẩn bớt
Mẹo: Hãy test dùng các hàm XLOOKUP và
XMATCH mới, phiên bạn dạng cải tiến của các hàm được thể hiện trong bài viết này. Những hàm bắt đầu này chuyển động theo bất kỳ hướng nào và trả về hiệu quả khớp đúng mực theo mang định, góp việc sử dụng chúng dễ ợt và dễ dãi hơn so với các hàm trước.
Bạn đang xem: Cách sử dụng hàm index và vlookup
Giả sử các bạn có list số vị trí văn phòng công sở và bạn nên biết những nhân viên cấp dưới nào vẫn ở trong những văn phòng. Bảng tính này vô cùng lớn, vị vậy bạn có thể nghĩ rằng đó là 1 trong nhiệm vụ cực nhọc khăn. Thực ra, chúng ta cũng có thể thực hiện khá thuận lợi với một hàm tra cứu.
Các hàm VLOOKUP với HLOOKUP , cùng rất INDEX và MATCH, là một số hàm có ích nhất trong Excel.
Dưới đây là ví dụ về cách sử dụng hàm VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
Trong lấy ví dụ như này, B2 là đối số đầu tiên—một thành phần dữ liệu mà hàm yêu cầu hoạt động. Đối với hàm VLOOKUP, đối số trước tiên này là giá trị mà bạn có nhu cầu tìm. Đối số này hoàn toàn có thể là một tham chiếu ô hoặc một giá trị cố định và thắt chặt như "smith" hoặc 21.000. Đối số lắp thêm hai là phạm vi ô, C2-:E7, nhằm tìm kiếm giá trị bạn muốn tìm. Đối số thứ tía 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 như khách hàng nhập TRUE, hoặc quăng quật trống đối số, hàm đã trả về công dụng gần khớp với giá trị chúng ta đã chỉ ra rằng trong đối số máy nhất. Nếu như bạn nhập FALSE, hàm đã khớp với cái giá trị nhưng đối số thứ nhất cung cấp. Nói biện pháp khác, việc để trống đối số thiết bị tư—hoặc nhập TRUE—cho phép bạn linh hoạt hơn.
Ví dụ này cho chính mình thấy cách thức mà hàm hoạt động. Khi bạn nhập cực hiếm vào ô B2 (đối số trang bị nhất), hàm VLOOKUP tìm kiếm kiếm những ô vào phạm vi C2:E7 (đối số 2nd) cùng trả về kết quả gần đúng tốt nhất từ cột thứ cha trong phạm vi, cột E (đối số trang bị 3).
Đối số thứ tư trống, vị vậy hàm trả về công dụng gần đúng. Giả dụ không, các bạn sẽ phải nhập trong số những giá trị vào cột C hoặc D để có được kết quả.
Khi đang cảm thấy dễ chịu với hàm VLOOKUP, dễ thực hiện hàm HLOOKUP. Các bạn nhập cùng các đối số, nhưng lại nó tìm kiếm kiếm trong sản phẩm thay vì cột.
Sử dụng hàm INDEX và MATCH thay bởi vì hàm VLOOKUP
Có những hạn chế nhất định khi áp dụng hàm VLOOKUP—hàm VLOOKUP chỉ có thể tra cứu quý hiếm từ trái sang trọng phải. Điều này tức là cột đựng giá trị mà các bạn tra cứu vãn phải luôn nằm ở phía trái cột bao gồm chứa giá trị trả về. Hiện nay nếu bảng tính của công ty không được xây dựng theo cách này, thì đừng sử dụng VLOOKUP. Thay vào đó, hãy sử dụng phối hợp hàm INDEX với MATCH.
Ví dụ này hiển thị một danh sách nhỏ dại mà trong những số đó giá trị mà họ muốn tra cứu kiếm, Chicago, không bên trong cột xung quanh cùng bên trái. Bởi vậy, chúng tôi không thể sử dụng hàm VLOOKUP. Cố vào đó, bọn họ sẽ áp dụng hàm MATCH nhằm tìm Chicago vào phạm vi B1:B11. Nó được tra cứu thấy trong sản phẩm 4. Sau đó, hàm INDEX sử dụng giá trị đó có tác dụng đối số tra cứu cùng tìm tập hợp mang lại Chicago trong cột thiết bị 4 (cột D). Phương pháp được thực hiện được hiển thị vào ô A14.
Bạn 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 rẽ mình, dưới đó là một số tài liệu mẫu.
Ví dụ về VLOOKUP tại địa điểm làm việc
Sao chép dữ liệu dưới đâ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 để độ rộng cột cho các cột tự A đến C thành 250 điểm hình ảnh và bấm Ngắt dòng (tab Trang đầu, team 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ị một trong các cột A, rồi tìm giá trị lớn nhất nhỏ rộng hoặc bằng 1 trong các 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ỏ rộng hoặc bằng một 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 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 vào cột A. Vì 0,1 nhỏ hơn giá trị nhỏ nhất trong cột A, lỗi được trả về. Xem thêm: Url là gì? cách nhập url cách lấy, tối ưu url chuẩn seo mới nhất | #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 vào cột A là 1,29 và sau đó trả về giá trị từ cột B vào cùng hàng. | 1,71 |
Ví dụ về HLOOKUP
Sao chép toàn bộ các ô trong bảng này cùng dán chúng nó vào ô A1 trên trang tính trống vào Excel.
Mẹo: Trước khi bạn dán dữ liệu vào Excel, hãy đặt độ rộng lớn cột cho những cột trường đoản cú A đến C thành 250 điểm hình ảnh và bấm Ngắt dòng (tab Trang đầu, team 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 vào 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 vào 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 vào cùng cột. Vì ko tìm thấy kết quả khớp chính xác với "B", cho nên vì thế giá trị lớn nhất vào hàng 1 mà nhỏ rộng "B" sẽ được dùng: "Trục," vào 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 vào hằng số mảng bố 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ó bố hàng chứa giá trị vào 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à vào cùng cột đó là 3, trả về "c" . | "c" |
Ví dụ về INDEX và MATCH
Ví dụ ở đầu cuối này sử dụng các hàm INDEX với MATCH cùng mọi người trong nhà để trả về số hóa đơn sớm nhất và ngày tương ứng cho từng thành phố trong các năm thành phố. Bởi vì ngày được trả về bên dưới dạng số, chúng tôi sử dụng hàm TEXT nhằm định dạng ngày tháng. Hàm INDEX thực thụ sử dụng hiệu quả của hàm MATCH để triển khai đố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 – trước tiên để trả về số hóa đối kháng rồi kế tiếp để trả về ngày tháng.
Sao chép tất cả các ô trong bảng này với dán chúng nó vào ô A1 bên trên trang tính trống vào Excel.
Mẹo: Trước khi chúng ta dán tài liệu vào Excel, hãy để độ rộng cột cho các cột trường đoản cú A mang lại D thành 250 điểm ảnh và bấm Ngắt dòng (tab Trang đầu, đội Căn chỉnh).
| Hóa đơn | Thành phố | Ngày lập Hóa đơn | Hóa đối chọi 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: " & 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: " và 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: " & 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: " và TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"d/m/yy") Hàm VLOOKUP là 1 trong hàm tra cứu kiếm cơ bạn dạng trong Excel, tuy vậy hàm này còn có khá nhiều hạn chế và chưa đáp ứng được những nhu cầu tìm kiếm nâng cấp hơn. Vào trường đúng theo này, chúng ta cũng có thể sử dụng phối kết hợp hàm INDEX với MATCH là hàm sửa chữa VLOOKUP. Hãy cùng cachseo.com mày mò trong nội dung bài viết dưới phía trên nhé! Cách sử dụng hàm INDEX MATCH vào ExcelTrước khi tìm hiểu về phép phối hợp hàm INDEX MATCH - hàm thay thế sửa chữa cho VLOOKUP, chúng ta sẽ lần lượt tò mò về công thức của những hàm tham chiếu này và giải pháp dùng cơ bản của bọn chúng trong Excel. Cách cần sử dụng hàm INDEX trong ExcelHàm INDEX được dùng để đưa và trả dữ liệu của một ô trong 1 vùng dựa vào chỉ số hàng còn chỉ số cột của vùng đó. Hàm INDEX tất cả cú pháp như sau: =INDEX(vùng_dữ_liệu, hàng_thứ_mấy, =INDEX(A1:H10,3,4)Kết quả Excel trả về cực hiếm là 840.000 trên ô D3 như vào hình dưới đây.  Cách cần sử dụng hàm MATCH vào ExcelHàm MATCH được áp dụng để kiếm tìm kiếm vị trí hoàn toàn có thể là kha khá hoặc giỏi đốicủa1 giá trị trong 1 dòng hoặc một cột củabảng tính. Cú pháp của hàm MATCH như sau: =MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, =MATCH("S04N",A1:A10,0)Kết quả Excel trả về bằng 6, nghĩa là cực hiếm "S04N" nằm tại vị trí dòng lắp thêm 6 vào phạm vi A1:A10.  Nếu chỉ dừng ở đây, bạn sẽ thấy hàm MATCH không có rất nhiều giá trị vào ứng dụng, mặc dù nhiên bạn sẽ thấy được xem hữu ích khi phối hợp hàm MATCH cùng hàm INDEX để triển khai hàm thay thế VLOOKUP. Để thành thạo tất cả các hàm từ bỏ cơ phiên bản đến nâng cao, phục vụ cho việc đo lường và tính toán và phân tích dữ liệu trên Excel, bạn có thể tham khảo khóa đào tạo Excel online sau trên cachseo.com: Bạn cũng có thểThíchBài Viết Liên Quan |
