Excel đến khanhhoatrip.com 365 Excel cho 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 cùng xmatch mới, các phiên bản được đổi mới của những hàm được mô tả trong bài viết này. Phần đông hàm mới chuyển động trong bất kỳ hướng nào với trả về các công dụng khớp đúng mực theo khoác định, giúp dễ dàng hơn và dễ ợt hơn khi thực hiện hơn người dùng của họ.

Bạn đang xem: Hàm dò tìm giá trị


Giả sử chúng ta có một danh sách các số vị trí Office với bạn nên biết những nhân viên nào đã ở trong những Office. Bảng tính là khôn xiết lớn, vày vậy chúng ta có thể nghĩ rằng đó là nhiệm vụ đầy thách thức. Thật tiện lợi để triển khai với hàm Lookup.

Hàm vlookup với hlookup , với chỉ mục cùng khớp,là một vài trong số các hàm có ích nhất vào Excel.


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

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

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

*

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

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

Sử dụng chỉ mục cùng khớp thay bởi VLOOKUP

Có một trong những giới hạn khăng khăng với việc sử dụng VLOOKUP — hàm VLOOKUP chỉ có thể tra cứu vớt một giá trị từ trái quý phái phải. Điều này tức là cột gồm chứa quý giá mà các bạn nhìn lên luôn luôn ở bên trái cột tất cả chứa giá trị trả về. Bây giờ nếu bảng tính của chúng ta không được xây dựng theo phong cách này, thì không áp dụng 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à cực 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 ngoài cùng mặt trái. Do vậy, shop chúng tôi không thể thực hiện VLOOKUP. Vậy vào đó, cửa hàng chúng tôi sẽ thực hiện hàm MATCH để 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ị chính là đối số tra cứu cùng tìm thấy dân số cho Chicago trong cột 4th (cột D). Cách làm được thực hiện được hiển thị vào ô A14.

*

Để hiểu thêm ví dụ về cách sử dụng chỉ mục và kết quả thay vị VLOOKUP, hãy xem nội dung bài viết https://www.mrexcel.com/Excel-Tips/Excel-vlookup-index-match/ theo Bill Jelen, khanhhoatrip.com 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 biệt bạn, đấy là một số dữ liệu mẫu.

Ví dụ về VLOOKUP tại chỗ làm việc

Sao chép tài liệu sau vào trong 1 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 những cột từ A mang đến điểm hình ảnh C cho 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ỏ 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 vào cùng hàng.

Xem thêm: Điện Thoại Đài Loan Là Sao ? Có Nên Mua Điện Thoại Đài Loan Hay Khô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 những 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 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 vào 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 vào cùng hàng.

1,71

Ví dụ HLOOKUP

Sao chép tất cả các ô vào bảng này cùng 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 từ A cho điểm hình ảnh C cho 250 cùng bấm ngắt cái 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 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", vì thế giá trị lớn nhất trong hàng 1 mà nhỏ rộng "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 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ó tía 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" vào hàng 2 và vào cùng cột đó là 3, trả về "c" .

"c"

Ví dụ về chỉ mục cùng khớp

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

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


Mẹo: Trước khi bạn dán tài liệu vào Excel, hãy đặt độ 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 cái 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ũ tuyệt 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 liên quan