Excel đến tuygiangho.vn 365 Excel cho web Excel 2021 Excel 2019 Excel năm nhâm thìn Excel 2013 coi thêm...Ít hơn

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

Bạn đang xem: Hàm tra cứu trong excel


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

Hàm vlookup và hlookup , cùng với chỉ mục với khớp,là một vài trong những các hàm có lợi 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 — 1 phần tử dữ liệu mà hàm phải hoạt động. Đối cùng với hàm VLOOKUP, đối số đầu tiên là cực hiếm mà bạn muốn tìm. Đối số này hoàn toàn có thể là tham chiếu ô hoặc một giá trị cố định chẳng hạn như "Smith" hoặc 21.000. Đối số vật dụng hai là phạm vi ô, C2-: E7, trong các số ấy để kiếm tìm kiếm cực hiếm mà bạn có nhu cầu tìm. Đối số thứ tía là cột vào phạm vi ô tất cả chứa cực hiếm 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 bạn nhập TRUE, hoặc quăng quật trống đối số, hàm đã trả về tác dụng gần khớp với cái giá trị các bạn đã đã cho thấy trong đối số lắp thêm nhất. Nếu như khách hàng nhập FALSE, hàm đang khớp với mức giá trị vày đối số trước tiên cung cấp. Nói bí quyết khác, hãy nhằm trống đối số thứ tứ — hoặc nhập TRUE — cung cấp cho mình tính hoạt bá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 sẽ tìm kiếm những ô trong phạm vi C2: E7 (đối số vật dụng hai) và trả về kết quả gần đúng độc nhất vô nhị từ cột thứ cha trong phạm vi, cột E (đối số thứ 3).

*

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

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

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

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

Ví dụ này hiển thị một danh sách nhỏ dại mà giá trị mà 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. Vày vậy, công ty 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 nhằm tìm Chicago vào phạm vi B1: B11. Nó được kiếm tìm thấy trong hàng 4. Sau đó, chỉ mục áp dụng giá trị đó là đối số tra cứu với tìm thấy dân số cho Chicago vào cột 4th (cột D). Cách làm được sử dụng được hiển thị vào ô A14.

*

Để hiểu thêm ví dụ về kiểu cách sử dụng chỉ mục và hiệu quả thay vì VLOOKUP, hãy xem bài viết https://www.mrexcel.com/Excel-Tips/Excel-vlookup-index-match/ theo Bill Jelen, tuygiangho.vn MVP.

Bạn thử đi

Nếu bạn muốn thử nghiệm với những hàm tra cứu giúp trước khi dùng thử với tài 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 khu vực làm việc

Sao chép tài liệu sau 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 trường đoản cú A mang đến điểm ảnh C đến 250 cùng bấm ngắt mẫu văn bản (tabtrang đầ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 những cột A, rồi tìm giá trị lớn nhất nhỏ rộng hoặc bằng 1 trong những 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: Combo Mô Hình Các Nhân Vật Trong Minecraft Ở Phiên Bản Rockbed Và Java

2,17

=VLOOKUP(1,A2:C10,3,TRUE)

Dùng khớp gần đúng để tìm kiếm giá trị một 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ộ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ì ko 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 vào 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 vào cùng hàng.

1,71

Ví dụ HLOOKUP

Sao chép toàn bộ các ô vào bảng này cùng 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 tài liệu vào Excel, hãy đặt độ rộng cột cho những cột trường đoản cú A mang lại điểm hình ảnh C cho 250 với bấm ngắt dòng 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 vào cùng cột. Vì không 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ỏ 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 vào 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ó 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" vào 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ụ cuối cùng này sử dụng những hàm INDEX với MATCH cùng với nhau nhằm trả về số hóa đơn sớm 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ề dưới dạng số, chúng tôi dùng hàm TEXT để định dạng nó là 1 trong những ngày. Hàm INDEX đích thực sử dụng kết quả của hàm MATCH để làm đối số. Việc phối hợp các hàm INDEX và MATCH được thực hiện hai lần trong những công thức – lần đầu tiên để trả về số hóa đối chọi rồi tiếp nối để trả về ngày tháng.

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


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 đến D cho 250 điểm ảnh và bấm ngắt loại văn bản (tabtrang đầu, team căn chỉnh ).


Hóa đơn

Thành phố

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

Hóa đơn cũ duy 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: " 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: " và TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"d/m/yy")

Bài viết liên quan