Top 8 # Hàm Match Và Index Trong Excel 2010 Xem Nhiều Nhất, Mới Nhất 2/2023 # Top Trend | Trucbachconcert.com

Cách Kết Hợp Hàm Index Và Match Trong Excel

Hàm tìm kiếm trong Excel thuộc nhóm hàm cơ bản Excel và có rất nhiều lựa chọn khác nhau để bạn tìm được giá trị mình cần trong bảng, như dùng hàm Vlookup Excel hay hàm Hlookup. Tuy nhiên 2 hàm tìm kiếm trên sẽ chỉ tìm giá trị theo hàng hoặc cột, theo 1 chiều mà không tìm kiếm giá trị trên cả hàng và cột.

Hướng dẫn kết hợp hàm Index và Match trong Excel

1. Hàm Index trong Excel

Hàm Index có 2 dạng là hàm Index dạng mảng và Index dạng tham chiếu.

Hàm Index dạng mảng trả về giá trị của 1 ô dữ liệu có chỉ số hàng và chỉ số cột đang tìm kiếm. Cú pháp hàm dạng mạng là Index (Array, Row_num,[column_num]). Trong đó:

Array: Mảng dữ liệu tham chiếu.

Row_num: Hàng chứa giá trị cần lấy.

Column_num: Cột chứa giá trị cần lấy.

Hàm Index dạng tham chiếu trả về giá trị của 1 ô có chỉ số hàng và cột đang tìm kiếm. Cú pháp hàm dạng tham chiếu là INDEX (Reference, Row_num, [Column_num], [Area_num]. Trong đó:

Reference: Vùng tham chiếu chứa giá trị cần tìm.

Row_num: Chỉ số hàng chứa giá trị cần tìm.

Column_num: Chỉ số cột chứa giá trị cần tìm.

Area_num: Số vùng trả về, để trống mặc định là 1.

Tìm hiểu thêm về cách sử dụng hàm Index trong bài viết Cách dùng hàm Index trong Excel.

2. Hàm Match trong Excel

Hàm Match trả về số thứ tự của giá trị cần tìm trong bảng. Cấu trúc hàm Match là =MATCH (Lookup_Value, Lookup_array, [Match_type]). Trong đó:

Lookup_Value: Giá trị cần tìm kiếm.

Lookup_array: Mảng chứa giá trị cần tìm.

Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:

Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.

Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.

Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.

Cách dùng hàm Match bạn đọc trong bài viết Cách dùng hàm Match trong Excel.

Bài tập 1: Tìm kiếm chất liệu của mặt hàng Tất cổ chân trong bảng (Tìm từ phải qua trái)

Theo bảng chúng ta thấy vị trí của Tất cổ chân nằm ở STT 4 và dóng sang sẽ thấy chất liệu của Tất cổ chân là Len dệt kim. Như vậy chúng ta sẽ nhìn vào cột Chất liệu và tìm tới dòng số 5 sẽ ra được kết quả.

Bước 1:

Trước hết chúng ta sẽ tìm mặt hàng Tất cổ chân nằm ở vị trí nào trong bảng. Nhập công thức =MATCH(“Tất cổ chân”,B1:B7,0) rồi nhấn Enter. Trong đó:

Tất cổ chân: Là giá trị cần tìm đúng vị trí.

B1:B7: Vùng tìm kiếm cho giá trị, ở đây là cột Mặt hàng.

0: Tìm giá trị chính xác.

Kết quả sẽ ra 5, nghĩa là Tất cổ chân ở vị trí dòng thứ 5 trong bảng.

Bước 2:

Bây giờ chúng ta sẽ tìm giá trị tại cột Chất liệu ứng với giá trị tại dòng số 5, sẽ ra được chất liệu cho mặt hàng Tất cổ chân.

Công thức kết hợp hàm Index với hàm Match là =INDEX( cột cần tra cứu giá trị, (MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)).

Áp dụng vào bài tập 1 thì có thể thay cả cụm MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)) = 5, thứ tự của giá trị dùng để tra cứu.

Nhập công thức là =INDEX(D1:D7,5) và nhấn Enter. Trong đó D1:D7 là cột chứa giá trị cần tra cứu.

Kết quả sẽ ra chất liệu Len dệt Kim.

Công thức tổng quát kết hợp hàm Index với hàm Match khi áp dụng vào bài tập 1 là =INDEX(D1:D7,MATCH(“Tất cổ chân”,B1:B7,0)) rồi nhấn Enter.

Kết quả cũng cho ra Len dệt kim ứng với Tất cổ chân.

Bài tập 2: Tìm mặt hàng ứng với chất liệu Vải lanh trong bảng (Tìm từ trái sang phải) Bước 1:

Trước hết chúng ta cần tìm vị trí của Vải lanh trong cột Chất liệu. Bạn nhập công thức =MATCH(“Vải lanh”,D1:D7,0) rồi nhấn Enter. Trong đó:

Vải lanh: Cần tìm vị trí cho giá trị này.

D1:D7: Vùng dữ liệu chứa giá trị cần tìm vị trí.

0: Tìm chính xác giá trị này.

Kết quả ra 7, Vải lanh nằm ở dòng số 7 tại cột Chất liệu.

Bước 2:

Kết hợp với hàm Index chúng ta có công thức là =INDEX(B1:B7,7) rồi nhấn Enter.

Kết quả sẽ hiển thị Quần nữ ngắn đúng với bảng.

Chúng ta sẽ kết hợp hàm Index với hàm Match trong cùng 1 công thức nhập là =INDEX(B1:B7,MATCH(“Vải lanh”,D1:D7,0)) rồi nhấn Enter.

Kết quả cũng cho ra ứng với Quần nữ ngắn.

Như vậy khi kết hợp hàm Index với hàm Excel thì chúng ta sẽ tìm kiếm được giá trị theo cả 2 chiều từ trái sang phải hay từ phải sang trái. Với hàm VLOOKUP, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải mà thôi.

Hàm Index Trong Excel Là Gì? Cách Kết Hợp Hàm Index Match

Hàm index trong Excel là gì

#1. Tổng quan về hàm INDEX

#1.1. Hàm INDEX là gì?

Hàm INDEX là hàm trả về mảng, giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Có thể hiểu, trong 1 mảng cho trước, nếu biết được vị trí (dòng và cột) của một phần tử thì hàm này sẽ giúp lấy giá trị tại ô đó.

#1.2. Hàm INDEX trong Excel là gì?

Hàm INDEX trong Excel là một Function (chức năng) được sử dụng nhằm mục đích trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng INDEX khi bạn biết (hoặc có thể tính toán) vị trí của một phần tử trong dải (bảng tính Excel) và bạn muốn nhận giá trị thực của phần tử đó.

#1.3. Hàm INDEX nâng cao

Hàm INDEX nâng cao giúp chúng ta có thể tạo một vùng tham chiếu rộng hơn, bao gồm nhiều vùng tham chiếu, công thức tổng quát như sau:

=INDEX(reference;row_num;[column_num],[area_num])

#2. Cú pháp và cách sử dụng hàm INDEX trong Excel

#2.1. Cú pháp hàm INDEX cơ bản

Công thức một hàm INDEX cơ bản như sau:

=INDEX(array;row_num;[column_num])

Trong đó:

– array: vùng ô hoặc một hàng số mảng nào đó;

– row_num: chọn hàng trong mảng từ đó trả về một giá trị;

– column: chọn cột trong mảng từ đó trả về một giá trị.

Bước 1: Tại ô G8 chúng ta viết công thức:

=INDEX(B8:F18;3;2)

Bước 2: Bấm phím Enter, ta có kết quả như sau: Phần tử ở dòng thứ 3, cột thứ 2 trong mảng là “Phạm Thanh Hồng”

Lưu ý: Lỗi #REF xảy ra khi các bạn chọn địa chỉ của phần tử không nằm trong mảng ban đầu.

#2.1. Cú pháp hàm INDEX nâng cao

Vượt ra phạm vi của 1 mảng, chúng ta có thể tìm phần tử của một vùng tham chiếu (có thể có 1 hoặc nhiều mảng khác nhau) thông qua công thức tổng quát như sau:

=INDEX(reference;row_num;[column_num],[area_num])

Trong công thức này:

– reference: vùng tham chiếu (có thể kết hơp nhiều mảng với nhau);

– row_num: chỉ số hàng từ đó trả về một tham chiếu;

– column_num: chỉ số cột từ đó trả về một tham chiếu;

– area_num: số của vùng ô sẽ trả về giá trị trong reference (tức là số thứ tự của mảng trong vùng tham chiếu). Nếu area_num được bỏ qua thì hàm INDEX mặc định dùng vùng 1.

Về cú pháp, không có sự khác nhau nhiều so với hàm cơ bản. Điểm khác nhau của công thức này là vùng giá trị có thể có nhiều hơn một mảng và kết quả trả về phụ thuộc vào mảng mà bạn chọn.

Để dễ hình dung, chúng ta cùng xem ví dụ sau.

Lúc này, chúng ta áp dụng công thức hàm INDEX nâng cao, để tìm kết quả theo điều kiện đề ra như sau:

=INDEX((B8:F12;B13:F18);3;2;2)

Ở trong công thức này, chúng ta có 2 mảng [B8:F12] và [B13:F18] (tức là vùng tham chiếu); phần tử dòng thứ 3, cột thứ 2 và nằm ở mảng thứ 2 do đó ta điền lần lượt là 3,2,2 trong công thức.

Sau đó, bấm phím Enter, kết quả của phép tính là:

Vậy, phần tử dòng thứ 3, cột thứ 2 của mảng thứ 2 là “Bùi Xuân Thắng”

Qua ví dụ trên, các bạn đã phân biệt được cách dùng và sự khác nhau giữa hàm INDEX cơ bản và nâng cao chưa nào.

#2.3. Kết hợp hàm INDEX MATCH

Thực tế, việc sử dụng hàm INDEX đơn thuần thường không tận dụng được hết sức mạnh của hàm này. Người ta thường kết hợp giữa hàm này và hàm MACTCH để thực hiện một chuỗi tính toán phức tạp hơn rất nhiều.

Như chúng ta đã biết, trong cú pháp hàm INDEX có 2 giá trị là vị trí dòng (row_num) và vị trí cột (column_num). Ta có thể thay thế 2 giá trị trên bằng cách sử dụng phép toán của hàm MATCH.

Như vậy, muốn tìm được chức vụ của nhân viên trong bảng đó, ta phải biết số dòng và số cột của phần tử đó để áp dụng công thức hàm INDEX như hướng dẫn phía trên.

Khi đó, để tìm được số dòng và số cột ta áp dụng thêm công thức MATCH.

Bước 1: Để tìm số dòng, ta phải tìm địa chỉ của mã “NV04” trong dãy mã nhân viên, công thức tìm như sau: =MATCH(“NV04”;B7:B17;0). Kết quả ra số 4. Vậy mã NV04 nằm ở dòng thứ 4 trong dãy mã NV

Bước 2: Để tìm số cột, ta phải tìm địa chỉ của “Chức vụ” trong dãy từ C6 đến F6, công thức tìm như sau: =MATCH(“Chức vụ”;C6:F6;0). Kết quả ra số 2. Vậy cụm từ “chức vụ” nằm ở vị trí thứ 2 trong dãy.

Bước 3: Ta kết hợp hàm INDEX và MATCH như sau:

=INDEX(C7:F17;MATCH(“NV04”;B7:B17;0);MATCH(“Chức vụ”;C6:F6;0))

Bước 4: Kết quả trả về, nhân viên có mã số NV04 làm chức vụ “Văn phòng”

Hỏi: Ý nghĩa của hàm INDEX?

Trả lời: Hàm này trả về giá trị của 1 phần tử trong mang tại một ô nào đó giao giữa cột và dòng.

Hỏi: Trong cú pháp hàm INDEX, vị trí dòng hay vị trí cột ở trước?

Hỏi: Phím tắt Filter trong Excel là gì?

Hỏi: Muốn tải file hướng dẫn các công thức ở trên thì tải ở đâu?

Cách Sử Dụng Hàm Match Và Index Trong Excel Và Kèm Ví Dụ

Nếu như bạn đang băn khoăn chưa biết về công thức cũng như cách sử dụng thì trong bài viết này chúng tôi sẽ chia sẻ về chi tiết cách sử dụng hai hàm là MATCH và INDEX và kèm ví dụ minh họa cụ thể trong phần nội dung dưới.

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

Sử dụng Hàm INDEX

Hàm INDEX có tác dụng trả về một giá trị hay một vùng dữ liệu tùy thuộc vào các tham số đầu vào. Hàm INDEX có hai cách sử dụng đó là dạng mảng (array) và dạng tham chiếu (reference).

1. INDEX dạng mảng

– Cú pháp:

=INDEX(array, row_num, column_num)

Trong đó:

array: Là một dãy ô (VD: A1:E5) hoặc là một hằng mảng (VD: {1;3;”a”;”TXD”})

row_num: số thứ tự dòng trong array để lấy giá trị trả về.

column_num: số thứ tự cột trong array để lấy giá trị trả về.

Lưu ý:

Nếu hai đối số column_num và row_num đều sử dụng, INDEX sẽ trả về giá trị của ô là giao điểm của column_num và row_num.

Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.

2. INDEX dạng tham chiếu

– Cú pháp:

=INDEX(reference, row_num, column_num, area_num)

Trong đó

reference: chứa các vùng tham chiếu. Nếu các vùng tham chiếu này rời rạc thì phải được để trong dấu ngoặc (). Ví dụ về reference: A1:F4; (A2:B3,T6:$X$5), ….

row_num: Là số thứ tự dòng trong tham chiếu.

column_num: Là số thứ tự cột trong tham chiếu.

area_num: số thứ tự vùng tham chiếu (được đánh số bắt đầu là 1, 2, 3, …) để trả về giao điểm của column_num và row_num trong dãy đó. Nếu bỏ qua area_num, INDEX sẽ mặc định dùng vùng 1.

Ví dụ, nếu reference = (A2:B4, C1:F4, G1:J5), thì vùng 1 là dãy A2:B4, vùng 2 là dãy C1:F4, và vùng 3 là dãy G1:J5.

Lưu ý:

Đối với tham chiếu chỉ gồm một dòng hoặc một cột, các bạn có thể sử dụng cú pháp:

INDEX(reference,,column_num); INDEX(reference,row_num,)

Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.

– Hàm MATCH trả về vị trí tương đối của một giá trị trong một dãy giá trị.

– Cú pháp:

=MATCH(lookup_value, lookup_array, match_type)

Trong đó

lookup_value: Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array. lookup_value có thể là một số, một chuỗi hoặc một tham chiếu. VD: “một”; 1; C4; …

lookup_array: vùng tìm kiếm

match_type: kiểu tìm kiếm.

Với match_type có thể là một trong các giá trị 1, 0 hoặc -1:

-1: MATCH sẽ tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự giảm dần từ lớn đến nhỏ.

0: MATCH sẽ tìm chính xác giá trị lookup_value. Với kiểu tìm kiếm này, MATCH không phân biệt chữ HOA hay chữ thường.

1: (mặc định) MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự tăng dần từ nhỏ đến lớn.

Lưu ý:

Nếu match_type = 0 và lookup_value kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value. Dấu * đại diện cho nhiều ký tự và dấu ? đại diện cho một ký tự.

Trong ví dụ sau đây, chúng ta có danh sách các học sinh với điểm thi tương ứng từng môn thi. Mỗi học sinh này đều thuộc một lớp chuyên nào đó và chúng ta sẽ sử dụng hàm INDEX để xác định điểm môn chuyên tương ứng với từng học sinh.

– Ô chứa điểm môn chuyên trong các môn thi của từng học sinh tương ứng với vị trí của môn chuyên trong danh sách các môn chuyên ở vùng D5:L5. Do đó, chúng ta sử dụng hàm MATCH để lấy về cột sẽ chứa điểm môn chuyên cho mỗi học sinh. Ví dụ, với học sinh Vũ Thanh Khiết, chúng ta sử dụng công thứcMATCH(C10;$D$5:$L$5;0).

Với mỗi học sinh:Lên đầu trang ↑

So sánh hàm INDEX + MATCH với VLOOKUP và VLOOKUP

– Hàm HLOOKUP và VLOOKUP chỉ sử dụng được nếu cột (dòng) chứa giá trị tìm kiếm nằm ở vị trí trước so với cột (dòng) chứa giá trị cần trả về. Nếu không, chúng ta sẽ cần phải sử dụng kết hợp với công thức mảng để lấy được giá trị. Tuy nhiên, như các bạn thấy với cặp MATCH + INDEX, chúng ta có thể lấy được giá trị cần tìm mà không cần quan tâm tới vị trí của vùng tìm kiếm và vùng kết quả như hai hàm VLOOKUP và HLOOKUP.

Hàm nhân trong Excel

Hàm tính điểm trung bình trong Excel

Sử dụng hàm Indirect trong Excel

Tải các tập tin minh hoạ

Lên đầu trang ↑

Lời kết

Kết Hợp Hàm Match Và Index Để Dò Tìm Trong Bảng Tính Excel

Như tiêu đề của bài viết, bài tập sau hướng dẫn các bạn sử dụng hàm INDEX và hàm MATCH để có thể dò tìm được giá trị trong excel. Việc dò tìm dữ liệu trong excel là công việc rất thường xuyên của các bạn học tin học, làm kế toán tại doanh nghiệp

Các ban có thể sử dụng Hàm VLOOKUP và hàm HLOOKUP để dò tìm, tuy nhiên, đặc điểm của loại dò tìm này là dữ liệu dùng để dò tìm nằm song song, hay nói cách khác dữ liệu dò tìm nằm cùng giá trị đơn với Mã dùng để so khớp, so sánh khi dò tìm lấy kết quả. Ở cách dò tìm này, ta chỉ có thể thực hiện so sánh mã hàng với một giá trị tương ứng ở bảng dò tìm duy nhất, vậy trường hợp ta cần lấy giá trị mà giá trị so khớp để lấy ra kết quả nằm trên cả hàng và cột thì sao, hãy đọc xuống bên dưới mục

Khóa học 100 thủ thuật excel tại: http://clevercfo.edu.vn/100-thu-thuat-excel-ke-toan-tai-chinh.html

Khóa học 100 thủ thuật excel tại: http://clevercfo.edu.vn/100-thu-thuat-excel-ke-toan-tai-chinh.html – See more at: http://clevercfo.edu.vn/he-thong-kiem-soat/43-tinh-hoa-hong-ban-hang-don-gian-bang-ham-lookup.html#sthash.f1iaQyFY.dpuf

Khóa học 100 thủ thuật excel tại: http://clevercfo.edu.vn/100-thu-thuat-excel-ke-toan-tai-chinh.html – See more at: http://clevercfo.edu.vn/he-thong-kiem-soat/43-tinh-hoa-hong-ban-hang-don-gian-bang-ham-lookup.html#sthash.f1iaQyFY.dpuf

Khóa học 100 thủ thuật excel tại: http://clevercfo.edu.vn/100-thu-thuat-excel-ke-toan-tai-chinh.html – See more at: http://clevercfo.edu.vn/he-thong-kiem-soat/43-tinh-hoa-hong-ban-hang-don-gian-bang-ham-lookup.html#sthash.f1iaQyFY.dpuf

Trong trường hợp dò tìm Phức tạo, nghĩa là dò tìm theo cả hàng và cả cột trong excel. Dò tìm theo hàng và cột là sao, có nghĩa là giá trị đó nằm tại điểm giao nhau của giá trị so khớp hàng và cột. Lúc này ta phải so khớp cả hai giá trị mới lấy ra được kết quả, nằm trên hàng và cột. Lúc này, sự kết hợp giữa hàm index và hàm match là giải pháp tuyệt vời nhất.

Đầu tiên các bạn phải hiểu cấu trúc kết hợp của hàm INDEX và hàm MATCH như sau:

Cấu trúc: = INDEX(Bảng dữ liệu cần lấy giá trị, MATCH(CỘT), MATCH(DÒNG))

Giải thích thêm:

MATCH(CỘT) = MATCH(Giá trị cột ở bảng cần so khớp với bảng giá trị, Cột ở bảng lấy giá trị so khớp, 0).

MATCH(DÒNG) = MATCH(Giá trị dòng ở bảng cần so khớp với bảng giá trị, Dòng ở bảng lấy giá trị so khớp, 0).

CleverCFO Education