Excel mang đến 91neg.com 365 Excel đến web Excel 2021 Excel 2019 Excel 2016 Excel 2013 coi thêm...Ít hơn

Mẹo: Hãy test sử dụng những hàm Xlookup và xmatch mới, những phiên phiên bản được cải tiến của các hàm được biểu hiện trong nội dung bài viết này. Hầu như hàm mới vận động trong bất kỳ hướng nào cùng trả về các hiệu quả khớp đúng đắn theo mang định, giúp dễ dàng hơn và dễ dàng hơn khi sử dụng hơn người dùng của họ.

Bạn đang xem: Hàm dò tìm trong excel


Giả sử chúng ta có một danh sách những số địa chỉ Office cùng bạn nên biết những nhân viên cấp dưới nào sẽ ở trong mỗi Office. Bảng tính là siêu lớn, vị vậy bạn có thể nghĩ rằng kia là trách nhiệm đầy thách thức. Thật dễ ợt để triển khai với hàm Lookup.

Hàm vlookup và hlookup , cùng rất chỉ mục và khớp,là một vài trong những các hàm bổ ích nhất trong Excel.


Dưới đấy là ví dụ về cách sử dụng VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

Trong ví dụ này, B2 là đối sốđầu tiên — một trong những phần tử tài liệu mà hàm đề nghị hoạt động. Đối cùng với hàm VLOOKUP, đối số đầu tiên là quý hiếm mà bạn có nhu cầu tìm. Đối số này rất có thể là tham chiếu ô hoặc một giá trị cố định và thắt chặt chẳng hạn như "Smith" hoặc 21.000. Đối số vật dụng hai là phạm vi ô, C2-: E7, trong những số đó để tra cứu kiếm quý hiếm mà bạn muốn tìm. Đối số thứ bố là cột vào phạm vi ô bao gồm chứa giá trị mà chúng ta 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 bỏ trống đối số, hàm sẽ trả về hiệu quả gần khớp với giá trị các bạn đã chỉ ra trong đối số lắp thêm nhất. Nếu bạn nhập FALSE, hàm vẫn khớp với cái giá trị vị đối số đầu tiên cung cấp. Nói biện pháp khác, hãy để trống đối số thứ bốn — hoặc nhập TRUE — cung cấp cho chính mình tính linh hoạt hơn.

Ví dụ này cho chính mình thấy phương thức mà hàm hoạt động. Khi chúng ta nhập một giá trị trong ô B2 (đối số đầu tiên), VLOOKUP vẫn tìm kiếm các ô vào phạm vi C2: E7 (đối số lắp thêm hai) cùng trả về công dụng gần đúng duy nhất từ cột thứ ba trong phạm vi, cột E (đối số trang bị 3).

*

Đối số thứ bốn là trống, bởi vì vậy hàm vẫn trả về một kết quả phù hợp gần đúng. Nếu không, các bạn sẽ phải nhập một trong những giá trị vào cột C hoặc D để có được kết quả.

Khi các bạn cảm thấy dễ chịu và thoải mái với hàm VLOOKUP, hàm HLOOKUP những dễ sử dụng. Bạn nhập những đối số tựa như nhưng kiếm tìm kiếm trong hàng thay vì chưng cột.

Sử dụng chỉ mục cùng khớp thay vì chưng VLOOKUP

Có một vài giới hạn một mực với việc áp dụng VLOOKUP — hàm VLOOKUP chỉ hoàn toàn có thể tra cứu giúp một giá trị từ trái sang phải. Điều này tức là cột bao gồm chứa quý hiếm mà chúng ta nhìn lên luôn ở phía bên trái cột tất cả chứa quý giá trả về. Hiện thời nếu bảng tính của bạn không được xây dựng theo cách này, thì không thực hiện VLOOKUP. Sử dụng kết hợp các hàm INDEX cùng MATCH cố vào đó.

Ví dụ này hiển thị một danh sách nhỏ dại mà quý giá mà shop chúng tôi muốn tìm kiếm kiếm, Chicago không tồn tại trong cột ko kể cùng mặt trái. Vì chưng vậy, shop chúng tôi không thể áp dụng VLOOKUP. Vậy 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 search thấy trong sản phẩm 4. Sau đó, chỉ mục sử dụng giá trị đó là đối số tra cứu với tìm thấy dân sinh cho Chicago trong cột 4th (cột D). Phương pháp được áp dụng được hiển thị trong ô A14.

*

Để hiểu biết thêm ví dụ về phong thái sử dụng chỉ mục và công dụng thay vày VLOOKUP, hãy xem bài viết https://www.mrexcel.com/Excel-Tips/Excel-vlookup-index-match/ theo Bill Jelen, 91neg.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ớt trước khi dùng thử với dữ liệu của riêng bạn, đấy là một số tài liệu mẫu.

Ví dụ về VLOOKUP tại vị trí 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 lớn cột cho những cột từ A đến điểm ảnh C mang lại 250 và bấm ngắt chiếc văn bản (tabtrang đầ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 những cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng một 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ị một 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 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ỏ rộng 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 vào 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ụ HLOOKUP

Sao chép tất cả các ô vào bảng này và dán chúng nó vào ô A1 trên trang tính trống trong Excel.


Mẹo: Trước khi chúng ta dán tài liệu vào Excel, hãy đặt độ rộng lớn cột cho các cột trường đoản cú A mang lại điểm ảnh C mang lại 250 với bấm ngắt dòng văn bản (tabtrang đầu, đội 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 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 vì thế 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 vào 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 tía hàng và trả về giá trị từ hàng 2 vào cùng cột (trong trường hợp này là cột thứ ba). Có tía 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ề chỉ mục cùng khớp

Ví dụ sau cuối này sử dụng các hàm INDEX cùng MATCH cùng với nhau nhằm trả về số hóa đơn sớm nhất và ngày khớp ứng của nó cho mỗi năm thành phố. Vì chưng ngày được trả về bên dưới dạng số, chúng tôi dùng hàm TEXT để định dạng nó là 1 ngày. Hàm INDEX thực sự sử dụng tác dụng của hàm MATCH để làm đối số. Việc phối kết hợp các hàm INDEX với MATCH được sử dụng hai lần trong mỗi công thức – thứ 1 để trả về số hóa đơn rồi tiếp đến để trả về ngày tháng.

Sao chép tất cả các ô trong bảng này cùng dán nó vào ô A1 trên trang tính trống trong Excel.

Xem thêm: Đề Thi Hk1 Toán 9 Có Đáp Án, Đề Thi Hk1 Toán Lớp 9 Các Quận Huyện Hà Nội 2019


Mẹo: Trước khi chúng ta dán dữ liệu vào Excel, hãy để độ rộng cột cho những cột từ bỏ A cho D mang đến 250 điểm ảnh và bấm ngắt mẫu văn bản (tabtrang đầu, nhóm căn chỉnh ).


Hóa đơn

Thành phố

Ngày lập Hóa đơn

Hóa solo 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: " 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: " và 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")