Kiến thức

Tra cứu giá trị bằng hàm VLOOKUP, INDEX hoặc MATCH-Excel

Mẹo: Hãy thử sử dụng các hàm

Xlookup

xmatch

mới, các phiên bản được cải tiến của các hàm được mô tả trong bài viết này. Những hàm mới hoạt động trong bất kỳ hướng nào và trả về các kết quả khớp chính xác theo mặc định, giúp dễ dàng hơn và thuận tiện hơn khi sử dụng hơn người dùng của họ.

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

Hàm

vlookup

hlookup

, cùng với

chỉ mục

khớp,

là một vài trong số các hàm hữu ích nhất trong Excel.

Lưu ý: Tính năng trình hướng dẫn tra cứu không còn sẵn dùng 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 phần tử dữ liệu mà hàm cần hoạt động. Đối với hàm VLOOKUP, đối số đầu tiên là giá trị mà bạn muốn tìm. Đối số này 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ố thứ hai là phạm vi ô, C2-: E7, trong đó để tìm kiếm giá trị mà bạn muốn tìm. Đối số thứ ba 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 bạn nhập TRUE, hoặc bỏ trống đối số, hàm sẽ trả về kết quả gần khớp với giá trị bạn đã chỉ ra trong đối số thứ nhất. Nếu bạn nhập FALSE, hàm sẽ khớp với giá trị do đố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 bạn thấy cách thức mà hàm hoạt động. Khi bạn nhập một giá trị trong ô B2 (đối số đầu tiên), VLOOKUP sẽ tìm kiếm các ô trong phạm vi C2: E7 (đối số thứ hai) và trả về kết quả gần đúng nhất từ cột thứ ba trong phạm vi, cột E (đối số thứ 3).

Cách dùng hàm VLOOKUP điển hình

Đối số thứ tư là trống, vì vậy hàm sẽ trả về một kết quả phù hợp gần đúng. Nếu không, bạn sẽ phải nhập một 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 đều dễ sử dụng. Bạn nhập các đối số tương tự nhưng tìm kiếm trong hàng thay vì cột.

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

Có một số giới hạn nhất định với việc sử dụng VLOOKUP — hàm VLOOKUP chỉ có thể tra cứu một giá trị từ trái sang phải. Điều này có nghĩa là cột có chứa giá trị mà bạn nhìn lên luôn ở bên trái cột có chứa giá trị trả về. Bây giờ 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 sử dụng VLOOKUP. Sử dụng kết hợp các hàm INDEX và MATCH thay vào đó.

Ví dụ này hiển thị một danh sách nhỏ mà giá trị mà chúng tôi muốn tìm kiếm, Chicago không có trong cột ngoài cùng bên trái. Vì vậy, chúng tôi không thể sử dụng VLOOKUP. Thay vào đó, chúng tôi sẽ sử dụng hàm MATCH để tìm Chicago trong phạm vi B1: B11. Nó được tìm thấy trong 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ông thức được sử dụng được hiển thị trong ô A14.

Dùng hàm INDEX và MATCH để tìm một giá trị

Để biết thêm ví dụ về cách sử dụng chỉ mục và kết quả thay vì VLOOKUP, hãy xem bài viết

https://www.mrexcel.com/Excel-Tips/Excel-vlookup-index-match/

theo Bill Jelen, Microsoft 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 bạn, đây là một số dữ liệu mẫu.

Bạn đang xem: Tra cứu giá trị bằng hàm VLOOKUP, INDEX hoặc MATCH-Excel

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

Sao chép dữ 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 đặt độ rộng cột cho các cột từ A đến điểm ảnh C đến 250 và bấm ngắt dòng 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 cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong 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ỏ hơn hoặc bằng 1 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 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 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 trong 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 ô trong bảng này và dán chúng vào ô A1 trên trang tính trống trong Excel.

Mẹo: Trước khi bạn dán dữ liệu vào Excel, hãy đặt độ rộng cột cho các cột từ A đến điểm ảnh C đến 250 và 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 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 giá trị lớn nhất trong 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 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ó ba 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 và khớp

Ví dụ cuối cùng này sử dụng các hàm INDEX và MATCH với nhau để trả về số hóa đơn sớm nhất và ngày tương ứng của nó cho mỗi năm thành phố. Vì ngày được trả về dưới dạng số, chúng tôi dùng hàm TEXT để định dạng nó là một ngày. Hàm INDEX thực sự sử dụng kết quả của hàm MATCH để làm đố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 – lần đầu để trả về số hóa đơn rồi sau đó để trả về ngày tháng.

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

Mẹo: Trước khi bạn dán dữ liệu vào Excel, hãy đặt độ rộng cột cho các cột từ A đến D đến 250 điểm ảnh và bấm ngắt dòng 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 đơn cũ nhất theo thành phố kèm theo ngày

3115

Atlanta

07/04/12

=”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

09/04/12

=”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

11/04/12

=”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

21/04/12

=”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

25/04/12

=”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”)

3331

Atlanta

27/04/12

3350

Atlanta

28/04/12

3390

Atlanta

01/05/12

3441

Atlanta

02/05/12

3517

Atlanta

08/05/12

3124

Austin

09/04/12

3155

Austin

11/04/12

3177

Austin

19/04/12

3357

Austin

28/04/12

3492

Austin

06/05/12

3316

Dallas

25/04/12

3346

Dallas

28/04/12

3372

Dallas

01/05/12

3414

Dallas

01/05/12

3451

Dallas

02/05/12

3467

Dallas

02/05/12

3474

Dallas

04/05/12

3490

Dallas

05/05/12

3503

Dallas

08/05/12

3151

New Orleans

09/04/12

3438

New Orleans

02/05/12

3471

New Orleans

04/05/12

3160

Tampa

18/04/12

3328

Tampa

26/04/12

3368

Tampa

29/04/12

3420

Tampa

01/05/12

3501

Tampa

06/05/12

Xem Thêm

Thẻ tham chiếu nhanh:

hàm tra cứu và tham chiếu của vlookup (tham khảo)

sử dụng đối số table_array trong hàm vlookup

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button