Note: The other languages of the website are Google-translated. Back to English
Đăng nhập  \/ 
x
or
x
Đăng ký  \/ 
x

or

Cách sử dụng INDEX và MATCH cùng nhau trong Excel

Khi làm việc với bảng Excel, bạn có thể liên tục tìm thấy các tình huống mà bạn cần phải tra cứu một giá trị. Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn cách áp dụng kết hợp các hàm INDEX và MATCH để thực hiện tra cứu theo chiều ngang và dọc, tra cứu hai chiều, tra cứu phân biệt chữ hoa chữ thường và tra cứu đáp ứng nhiều tiêu chí.

Hàm INDEX và MATCH làm gì trong Excel

Cách sử dụng hàm INDEX và MATCH cùng nhau


Hàm INDEX và MATCH làm gì trong Excel

Trước khi chúng ta sử dụng hàm INDEX và MATCH, hãy đảm bảo rằng chúng ta biết cách INDEX và MATCH có thể giúp chúng ta tìm kiếm các giá trị như thế nào trước tiên.

Cách sử dụng hàm INDEX trong Excel

Mô hình INDEX hàm trong Excel trả về giá trị tại một vị trí nhất định trong một phạm vi cụ thể. Cú pháp của hàm INDEX như sau:

=INDEX(array, row_num, [column_num])
  • mảng (bắt buộc) đề cập đến phạm vi mà bạn muốn trả về giá trị từ đó.
  • row_num (bắt buộc, trừ khi có column_num) đề cập đến số hàng của mảng.
  • cột_num (tùy chọn, nhưng bắt buộc nếu row_num bị bỏ qua) đề cập đến số cột của mảng.

Ví dụ, để biết điểm thi cuối cùng của Jeff, sinh viên thứ 6 trong danh sách, bạn có thể sử dụng hàm INDEX như sau:

= INDEX (E2: E11, 6) >>> trả lại 60

kết hợp chỉ số excel 01

√ Lưu ý: Phạm vi E2: E11 là nơi liệt kê bài kiểm tra cuối cùng, trong khi số 6 tìm điểm thi của 6sinh viên thứ.

Sau đây chúng ta hãy làm một bài kiểm tra nhỏ. Đối với công thức = INDEX (B2: E2,3), nó sẽ trả về giá trị nào? --- Vâng, nó sẽ trở lại Trung Quốc, Các 3rd giá trị trong phạm vi đã cho.

Bây giờ chúng ta nên biết rằng hàm INDEX có thể hoạt động hoàn hảo với phạm vi ngang hoặc dọc. Nhưng điều gì sẽ xảy ra nếu chúng ta cần nó để trả về một giá trị trong một phạm vi lớn hơn với nhiều hàng và cột? Trong trường hợp này, chúng ta nên áp dụng cả số hàng và số cột. Ví dụ, để tìm hiểu đất nước Emily đến từ với INDEX, chúng ta có thể định vị giá trị với số hàng là 8 và số cột là 3 trong các ô từ B2 đến E11 như sau:

= INDEX (B2: E11,8,3) >>> trả lại Trung Quốc

kết hợp chỉ số excel 02

Theo các ví dụ trên, về hàm INDEX trong Excel, bạn nên biết rằng:

  • Hàm INDEX có thể hoạt động với phạm vi dọc và ngang.
  • Hàm INDEX không phân biệt chữ hoa chữ thường.
  • Số hàng đi trước số cột (nếu bạn cần cả hai số) trong công thức INDEX.

Tuy nhiên, đối với một cơ sở dữ liệu thực sự lớn với nhiều hàng và cột, chắc chắn chúng ta không thể áp dụng công thức với số hàng và số cột chính xác. Và đây là lúc chúng ta nên kết hợp sử dụng hàm MATCH.

Bây giờ, chúng ta hãy tìm hiểu về những điều cơ bản của hàm MATCH trước.


Cách sử dụng hàm MATCH trong Excel

Hàm MATCH trong Excel trả về một giá trị số, vị trí của một mục cụ thể trong phạm vi nhất định. Cú pháp của hàm MATCH như sau:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (bắt buộc) đề cập đến phạm vi ô mà bạn muốn MATCH tìm kiếm.
  • Loại so khớp (không bắt buộc), 1, 0 or -1:
  • 1(mặc định), MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Các giá trị trong lookup_array phải được đặt theo thứ tự tăng dần.
  • 0, MATCH sẽ tìm giá trị đầu tiên chính xác bằng lookup_value. Các giá trị trong lookup_array có thể theo thứ tự bất kỳ. (Đối với trường hợp loại đối sánh được đặt thành 0, bạn có thể sử dụng các ký tự đại diện.)
  • -1, MATCH sẽ tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Các giá trị trong lookup_array phải được đặt theo thứ tự giảm dần.

Ví dụ, để biết vị trí của Vera trong danh sách những cái tên, bạn có thể sử dụng công thức MATCH như sau:

= MATCH ("vera", C2: C11,0) >>> trả lại 4

kết hợp chỉ số excel 03

√ Lưu ý: Hàm MATCH không phân biệt chữ hoa chữ thường. Kết quả “4” chỉ ra rằng cái tên “Vera” nằm ở vị trí thứ 4 của danh sách. “0” trong công thức là kiểu so khớp sẽ tìm giá trị đầu tiên trong mảng tra cứu bằng chính xác với giá trị tra cứu “Vera”.

Để biết vị trí của điểm “96” trong hàng từ B2 đến E2, bạn có thể sử dụng MATCH như thế này:

= MATCH (96, B2: E2,0) >>> trả lại 4

kết hợp chỉ số excel 04

☞ Những điều chúng ta nên biết về hàm MATCH trong Excel:

  • Hàm MATCH trả về vị trí của giá trị tra cứu trong mảng tra cứu, không trả về chính giá trị đó.
  • Hàm MATCH trả về kết quả phù hợp đầu tiên trong trường hợp trùng lặp.
  • Cũng giống như hàm INDEX, hàm MATCH cũng có thể hoạt động với phạm vi dọc và ngang.
  • MATCH cũng không phân biệt chữ hoa chữ thường.
  • Nếu giá trị tra cứu của công thức MATCH ở dạng văn bản, hãy đặt nó trong dấu ngoặc kép.

Bây giờ chúng ta đã biết về cách sử dụng cơ bản của các hàm INDEX và MATCH trong Excel, chúng ta hãy xắn tay áo lên và sẵn sàng kết hợp hai hàm.


Cách sử dụng hàm INDEX và MATCH cùng nhau

Trong phần này, chúng ta sẽ nói về các trường hợp khác nhau để sử dụng các hàm INDEX và MATCH để đáp ứng các nhu cầu khác nhau.

Ví dụ để kết hợp INDEX và MATCH

Vui lòng xem ví dụ dưới đây để tìm ra cách chúng tôi có thể kết hợp các hàm INDEX và MATCH:

Ví dụ, để biết Điểm thi cuối kỳ của Evelyn, chúng ta nên sử dụng công thức:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> trả lại 90

kết hợp chỉ số excel 05

Chà, vì công thức có thể trông phức tạp, chúng ta hãy xem xét từng phần của nó.

kết hợp chỉ số excel 06

Như bạn có thể thấy ở trên, INDEX công thức chứa ba đối số:

  • mảng: A2: D11 yêu cầu INDEX trả về giá trị phù hợp từ các ô thông qua A2 đến D11.
  • row_num: MATCH ("evelyn", B2: B11,0) cho INDEX biết hàng chính xác của giá trị.
  • Về công thức MATCH, chúng ta có thể giải thích nó như sau: để trả về vị trí của giá trị đầu tiên chính xác bằng “evelyn” trong các ô từ B2 đến B11 trong một giá trị số, đó là 5.
  • cột_num: MATCH ("bài thi cuối kỳ", A1: D1,0) cho INDEX biết cột chính xác của giá trị.
  • Về công thức MATCH, chúng ta có thể giải thích nó như sau: để trả về vị trí của giá trị đầu tiên chính xác bằng "bài kiểm tra cuối cùng" trong các ô từ A1 đến D1 ở một giá trị số, đó là 4.

Vì vậy, bạn có thể thấy công thức lớn đơn giản như công thức chúng tôi hiển thị bên dưới:

= INDEX (A2: D11,5,4)

Trong ví dụ này, chúng tôi đã sử dụng các giá trị được mã hóa cứng, "evelyn" và "kỳ thi cuối kỳ". Tuy nhiên, trong một công thức lớn như vậy, chúng ta không muốn các giá trị được mã hóa cứng vì chúng ta sẽ phải thay đổi chúng mỗi khi chúng ta tìm kiếm một thứ gì đó mới. Trong những trường hợp như vậy, chúng ta có thể sử dụng tham chiếu ô để làm cho công thức động như sau:

= INDEX (A2: D11,TRẬN ĐẤU(G2, B2: B11,0),TRẬN ĐẤU(F3, A1: D1,0))

kết hợp chỉ số excel 07


INDEX và MATCH để áp dụng tra cứu bên trái

Bây giờ, giả sử bạn cần biết lớp của Evelyn, làm thế nào chúng ta có thể sử dụng INDEX và MATCH để biết câu trả lời? Nếu bạn chú ý, bạn sẽ nhận thấy rằng cột lớp nằm ở phía bên trái của cột tên và nằm ngoài khả năng của một hàm tra cứu mạnh mẽ khác của Excel, hàm VLOOKUP.

Trên thực tế, khả năng tra cứu bên trái là một trong những khía cạnh mà sự kết hợp giữa INDEX và MATCH vượt trội hơn so với hàm VLOOKUP.

Để biết Lớp của Evelyn, tất cả những gì bạn cần làm là thay đổi giá trị trong ô F3 thành “Class” và sử dụng công thức tương tự như hình trên, các hàm INDEX và MATCH sau đó sẽ cho bạn biết câu trả lời ngay lập tức:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> trả lại A

kết hợp chỉ số excel 08

Nếu bạn có instaldẫn Kutools cho Excel, một bổ trợ Excel chuyên nghiệp được phát triển bởi nhóm của chúng tôi, bạn cũng có thể áp dụng tra cứu bên trái cho các giá trị được chỉ định với LOOKUP từ phải sang trái tính năng với vài cú nhấp chuột. Để triển khai tính năng này, vui lòng chuyển đến Kutools trong excel của bạn, tìm Công thức nhóm và nhấp vào LOOKUP từ phải sang trái trên danh sách thả xuống của SIÊU NHÌN. Bạn sẽ thấy một hộp thoại bật lên như thế này:

kết hợp chỉ số excel 09

Nhấp vào đây để biết các bước cụ thể để áp dụng tính năng tra cứu bên trái với Kutools cho Excel.


INDEX và MATCH để áp dụng tra cứu hai chiều

Bây giờ, bạn có thể tạo công thức kết hợp INDEX và MATCH với các giá trị tra cứu động để áp dụng tra cứu hai chiều không? Hãy thực hành tạo công thức trong các ô G3, G4 và G5 như hình dưới đây:

kết hợp chỉ số excel 10

Đây là những câu trả lời:

Ô G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Ô G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Ô G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Lưu ý: Sau khi áp dụng các công thức, bạn có thể dễ dàng lấy thông tin của bất kỳ học sinh nào bằng cách thay đổi tên trong ô G2.


INDEX và MATCH để áp dụng tra cứu phân biệt chữ hoa chữ thường

Từ các ví dụ trên, chúng ta biết rằng hàm INDEX và MATCH không phân biệt chữ hoa chữ thường. Tuy nhiên, trong trường hợp bạn cần công thức để phân biệt các ký tự viết hoa và viết thường, bạn có thể thêm CHÍNH XÁC hàm cho các công thức của bạn như thế này:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Lưu ý: Đây là công thức mảng yêu cầu bạn nhập với Ctrl + Shift + Enter. Sau đó, một cặp dấu ngoặc nhọn sẽ hiển thị trên thanh công thức.

Ví dụ, để biết Điểm thi của JIMMY, sử dụng các chức năng như sau:

kết hợp chỉ số excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> trả lại 86

Hoặc bạn có thể sử dụng tham chiếu ô:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> trả lại 86
Lưu ý: Đừng quên nhập với Ctrl + Shift + Enter.


INDEX và MATCH để áp dụng tra cứu với nhiều tiêu chí

Khi xử lý một cơ sở dữ liệu lớn với một số cột và chú thích hàng, luôn luôn khó khăn để tìm ra thứ gì đó đáp ứng nhiều điều kiện. Trong trường hợp này, vui lòng xem công thức bên dưới để tra cứu nhiều tiêu chí:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Lưu ý: Đây là công thức mảng yêu cầu bạn nhập với Ctrl + Shift + Enter. Sau đó, một cặp dấu ngoặc nhọn sẽ hiển thị trên thanh công thức.

Ví dụ, để tìm điểm thi cuối cùng của lớp A Coco đến từ Ấn Độ, công thức như sau:

kết hợp chỉ số excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> trả lại 88
√ Lưu ý: Đừng quên nhập bằng Ctrl + Shift + Enter.

Chà, điều gì sẽ xảy ra nếu bạn liên tục quên sử dụng Ctrl + Shift + Enter để hoàn thành công thức để công thức trả về kết quả không chính xác? Ở đây chúng tôi có một công thức phức tạp hơn, bạn có thể hoàn thành một công thức đơn giản Đi vào Chìa khóa:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Đối với ví dụ tương tự ở trên để tìm điểm thi cuối cùng của lớp A Coco đến từ Ấn Độ, công thức chỉ cần một Đi vào hit như sau:

kết hợp chỉ số excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> trả lại 88

Ở đây, chúng tôi sẽ không sử dụng các giá trị được mã hóa cứng, vì chúng tôi sẽ muốn có một công thức chung trong trường hợp có nhiều tiêu chí. Chỉ bằng cách này, chúng ta có thể dễ dàng nhận được kết quả mong muốn bằng cách thay đổi các giá trị trong các ô G2, G3, G4 trong ví dụ trên.

Với Kutools cho Excel's Tính năng Tra cứu đa điều kiện, bạn có thể tra cứu các giá trị cụ thể với nhiều tiêu chí trong vài cú nhấp chuột. Để triển khai tính năng này, vui lòng chuyển đến Kutools trong excel của bạn, tìm Công thức nhóm và nhấp vào Tra cứu đa điều kiện trên danh sách thả xuống của SIÊU NHÌN. Sau đó, bạn sẽ thấy một hộp thoại bật lên như hình dưới đây:

kết hợp chỉ số excel 14

Nhấp vào đây để biết các bước cụ thể để áp dụng tính năng tra cứu đa điều kiện với Kutools cho Excel.


INDEX và MATCH để áp dụng tra cứu trên nhiều cột

Nếu chúng ta có một bảng tính Excel với các cột khác nhau có chung một chú thích như hình dưới đây, làm thế nào chúng ta có thể khớp tên của mỗi học sinh với lớp của mình bằng INDEX và MATCH?

kết hợp chỉ số excel 15

Sau đây, hãy để tôi chỉ cho bạn cách hoàn thành nhiệm vụ bằng công cụ chuyên nghiệp của chúng tôi Kutools cho Excel. Với nó Người trợ giúp Công thức, bạn có thể nhanh chóng đối sánh sinh viên với các lớp học của họ như các bước được hiển thị bên dưới:

1. Chọn ô đích mà bạn muốn áp dụng chức năng.

2. Bên dưới Kutools tab, đi tới Công thức trợ giúp, Click Công thức trợ giúp trên danh sách thả xuống.

kết hợp chỉ số excel 16

3. Chọn Tra cứu từ Loại công thức, sau đó nhấp vào Lập chỉ mục và đối sánh trên nhiều cột.

kết hợp chỉ số excel 17

4. a. Nhấp vào đầu tiên biểu tượng đối sánh chỉ mục excelnút ở bên phải của Lookup_col để chọn các ô mà bạn muốn trả về giá trị, tức là tên lớp. (Bạn chỉ có thể chọn một cột hoặc một hàng ở đây.)
    NS. Nhấp vào thứ 2 biểu tượng đối sánh chỉ mục excelnút ở bên phải của Table_rng để chọn các ô để khớp với các giá trị trong Lookup_col, tức là, tên của học sinh.
    NS. Nhấp vào thứ 3 biểu tượng đối sánh chỉ mục excelnút ở bên phải của Lookup_value để chọn ô cần tra cứu, tức là tên của học sinh mà bạn muốn đối sánh với lớp của học sinh đó.

kết hợp chỉ số excel 18

5. Nhấp Ok, bạn sẽ thấy tên lớp của Jimmy hiển thị trong ô đích.

kết hợp chỉ số excel 19

6. Bây giờ bạn có thể kéo chốt điền xuống để điền vào các lớp học của sinh viên khác.

kết hợp chỉ số excel 20

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.



  • Super Formula Bar (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
tab kte 201905
  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.