Tìm kiếm và truy xuất dữ liệu trong Google Sheets có thể trở nên rất khó khăn khi bạn có một lượng lớn dữ liệu. Vì vậy, ngày hôm nay, chúng ta sẽ tìm hiểu về cách sử dụng INDEX MATCH trong Google Sheets, một công cụ mạnh mẽ để tìm kiếm và truy xuất dữ liệu một cách tự động.
Tại sao sử dụng INDEX và MATCH trong Google Sheets?
Với một cách đơn giản, các hàm tìm kiếm trong Google Sheets rất hữu ích khi bạn có một lượng lớn dữ liệu. INDEX và MATCH, một cách riêng biệt, cho phép bạn tìm kiếm dữ liệu một cách tự động mà không cần phải tìm kiếm thủ công.
Khám phá INDEX MATCH trong Google Sheets
Trước khi chúng ta đi vào chi tiết về cú pháp và định nghĩa của INDEX và MATCH, hãy xem qua một video giải thích quá trình này.
Hàm MATCH trong Google Sheets trả về vị trí tương đối hoặc “INDEX” của một phần tử trong một phạm vi ô. Nó lấy một phạm vi ô và một giá trị, và trả về vị trí của giá trị đó trong phạm vi ô. Ngược lại, hàm INDEX trong Google Sheets truy xuất giá trị chính xác dựa trên việc dịch chuyển dòng và cột.
Cú pháp của hàm INDEX
Để hiểu bất kỳ hàm nào trong Google Sheets, bạn cần hiểu cú pháp trước. Vì vậy, chúng ta hãy tìm hiểu cú pháp của hàm INDEX trước. Nó chỉ ra cách công thức trích xuất dữ liệu trong bảng tính của bạn và những gì bạn cần nhập để có kết quả mong muốn.
Dưới đây là cú pháp của công thức INDEX:
=INDEX(reference, [row], [column])- reference: Đây là phạm vi mà bạn muốn tìm kiếm. Đây là một yếu tố bắt buộc.
- row: Đây đại diện cho số dòng di chuyển từ ô đầu tiên của phạm vi của bạn. Tùy chọn: 0 nếu bỏ qua.
- column: Tương tự như dòng, nó đề cập đến số cột di chuyển. Cũng tùy chọn: 0 nếu bỏ qua.
Với hàm INDEX, chúng ta có thể tìm thấy giá trị trong ô tại một vị trí cụ thể. Hãy xem ví dụ bảng tính dưới đây:
Đối với ví dụ này, chúng ta sẽ sử dụng công thức sau:
=INDEX(A2:E6,2,2)Công thức này trả về giá trị của ô nằm ở hàng thứ hai và cột thứ hai của phạm vi được chỉ định.
Cú pháp của hàm MATCH
Chúng ta đã xem xét cú pháp của hàm đầu tiên. Vậy hàm tiếp theo là gì? Hàm MATCH trong Excel là một công cụ mạnh mẽ cho phép bạn tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về vị trí tương đối của giá trị đó trong phạm vi.
Dưới đây là cú pháp của hàm MATCH trong Google Sheets:
=MATCH(search_key, range, search_type)- search_key: Đây là phần tử mà chúng ta muốn tìm kiếm. Điều này có thể là văn bản, giá trị số, tham chiếu ô hoặc công thức.
- range: Đây là phạm vi ô mà chúng ta muốn tìm kiếm một phần tử phù hợp với search_key.
- search_type: Đây là một tham số tùy chọn. Nó xác định loại kết quả tìm kiếm chúng ta muốn. Nó có thể có một trong các giá trị sau:
- 0: Giá trị này chỉ định rằng tìm kiếm phải được thực hiện cho một phần tử chính xác. Tùy chọn này thường được sử dụng khi phạm vi chưa được xếp theo một trật tự cụ thể nào đó.
- 1: Đây là giá trị mặc định. Tùy chọn này giả định rằng phạm vi đã được sắp xếp theo thứ tự tăng dần. Bằng cách chỉ định tham số này là 1, chúng ta sẽ nhận được giá trị lớn nhất nhỏ hơn hoặc bằng search_key.
- -1: Tùy chọn này giả định rằng phạm vi đã được sắp xếp theo thứ tự giảm dần. Bằng cách chỉ định tham số này là -1, chúng ta sẽ nhận được giá trị nhỏ nhất lớn hơn hoặc bằng search_key.
Khác với hàm INDEX, MATCH trả về vị trí của giá trị tìm kiếm trong phạm vi. Hãy xem ví dụ dưới đây:
Công thức trên trả về giá trị 4, và theo phạm vi chúng ta đã chọn, từ Samsung được đặt trong ô thứ 4.
Bây giờ chúng ta đã xem xét cú pháp của cả hai hàm này, chúng ta biết rằng chúng có ích khi sử dụng riêng lẻ, nhưng khi kết hợp chúng lại, chúng cung cấp một phương pháp tuyệt vời thay thế cho các công cụ khác như VLOOKUP.
Kết hợp INDEX và MATCH
Công thức INDEX MATCH hoạt động tốt hơn khi được kết hợp với nhau. Chúng ta cần biết cú pháp của cả hai công thức được kết hợp.
Đầu tiên, khi bạn kết hợp INDEX và MATCH trong Google Sheets, bạn sẽ bắt đầu với công thức của hàm INDEX. Phần MATCH trong công thức điền vào đối số vị trí.
Khi hai hàm được kết hợp, cú pháp trông như sau:
INDEX(đối tượng_trả_về, MATCH(giá_trị_tìm_kiếm, phạm_vi_tìm_kiếm))Khi được kết hợp, công thức INDEX MATCH trong Google Sheets có thể tìm kiếm giá trị trong một ô của một bảng và trả về giá trị tương ứng trong một ô khác cùng hàng hoặc cột.
Nếu bạn còn hơi mơ hồ, đừng lo lắng. Chúng ta sẽ xem xét một ví dụ thực tế.
Hướng dẫn sử dụng INDEX MATCH trong Google Sheets từng bước
Như đã hứa, tôi sẽ cung cấp hướng dẫn từng bước về cách sử dụng INDEX MATCH trong Google Sheets. Hướng dẫn này sử dụng các hình ảnh do tôi tự tạo và trình bày một ví dụ thực tế về kiểm kê hàng hóa.
Bạn có muốn tìm hiểu cách sử dụng INDEX MATCH với nhiều tiêu chí? Tôi cũng có một video chi tiết hơn về điều này.
Hãy theo tôi khi tôi chỉ cho bạn cách sử dụng bảng kiểm kê và hàm INDEX MATCH trong Google Sheets để tìm số lượng hàng hóa theo tên công ty.
Bước 1: Điều hướng đến một ô trống và sử dụng cú pháp sau:
=INDEX(phạm_vi_ô,MATCH("giá_trị",phạm_vi_ô,0))
Trong ảnh chụp màn hình trên, tôi đã sử dụng công thức sau:
=INDEX(B3:B6,MATCH("Apple",A3:A6,0))Để tìm các giá trị trong phạm vi ô B3:B6 và MATCH với tên công ty “Apple” trong phạm vi ô A3:A6.
Thêm danh sách thả xuống để dễ dàng tìm kiếm
Bước 1: Điều hướng đến một ô trống, nhấp chuột phải, chọn “Hiển thị các hoạt động khác trên các ô”, sau đó nhấp vào “Xác nhận dữ liệu”. Từ đó, hãy chọn phạm vi ô A3:A6 và nhấp “Lưu”.
Bước 2: Tiếp theo, hãy điều hướng đến ô chứa danh sách thả xuống và thêm công thức sau:
=INDEX(B3:B6,MATCH(B9,A3:A6,0))
Trong ảnh chụp màn hình trên, tôi đã sử dụng ô B9 như là khóa MATCH, vì đó là nơi tôi đã tạo danh sách thả xuống. Mỗi khi tôi chọn một công ty khác trong danh sách thả xuống, Google Sheets sẽ hiển thị giá trị tương ứng với công ty đó. Điều này giúp tôi không cần thay đổi công thức khi tìm kiếm một công ty khác.
Đó là tất cả về việc sử dụng INDEX MATCH trong Google Sheets. Bạn có thể sử dụng chức năng này trên bảng tính Google của mình để dễ dàng tìm kiếm, trong khi thêm danh sách thả xuống sẽ chắc chắn hữu ích!
Tìm kiếm phân biệt chữ hoa chữ thường với VLOOKUP bằng INDEX MATCH trong Google Sheets
Trong Google Sheets, chức năng VLOOKUP mặc định không phân biệt chữ hoa và chữ thường. Tuy nhiên, bạn có thể tạo một tìm kiếm VLOOKUP phân biệt chữ hoa chữ thường bằng cách sử dụng các công thức INDEX và MATCH kết hợp với hàm EXACT. Hàm EXACT so sánh hai chuỗi ký tự và trả về TRUE nếu chúng giống nhau (bao gồm cả chữ hoa và chữ thường), hoặc FALSE nếu chúng khác nhau.
Trong ví dụ của chúng tôi, chúng tôi muốn tìm số lượng điện thoại của các sản phẩm Samsung viết thường:
Sau đây là cách bạn có thể tạo một tìm kiếm VLOOKUP phân biệt chữ hoa chữ thường bằng INDEX MATCH trong Google Sheets trong bảng tính ví dụ của chúng tôi. Gõ công thức sau:
=INDEX(B2:B9, MATCH(TRUE, EXACT(A13, A2:A9), 0))
Sử dụng FIND với INDEX MATCH
Một cách khác để tìm kiếm VLOOKUP phân biệt chữ hoa và chữ thường trong Google Sheets là thêm hàm FIND vào công thức INDEX MATCH.
Công thức được sử dụng trong ví dụ này như sau:
=ArrayFormula(INDEX(B3:B9, MATCH(1, FIND(A13, A3:A9)), 0))Hàm FIND tìm kiếm trong phạm vi giá trị có cùng chữ hoa và viết thường với khóa tìm kiếm. Trong trường hợp này, giá trị là “Samsung”. Hàm INDEX sau đó trả về giá trị tương ứng trong phạm vi số liệu, trong trường hợp của chúng tôi, nằm trong cột B. 0 trả về một kết quả khớp hoàn toàn.
Các phương pháp khác để tìm kiếm các bảng khác nhau
Có nhiều cách để tìm kiếm dữ liệu trong các bảng khác nhau. Mỗi phương pháp có một chức năng khác nhau. Dưới đây là ba cách phổ biến nhất để tìm kiếm các bảng khác nhau trong Excel và Google Sheets:
- INDEX và MATCH
- VLOOKUP
- XLOOKUP
INDEX và MATCH, VLOOKUP và XLOOKUP đều được sử dụng để tìm kiếm và trả về kết quả. Mỗi hàm yêu cầu cú pháp riêng để công thức hoạt động. Nhưng bạn nên sử dụng công cụ nào và khi nào?
Công thức VLOOKUP
Chúng tôi đã xem xét INDEX và MATCH, nhưng chúng tôi chưa giải thích thực sự cách VLOOKUP hoạt động. Trong nhiều năm qua, hàm VLOOKUP đã trở thành một công cụ tìm kiếm phổ biến trong Google Sheets và Excel.
Vì V đại diện cho dọc, bạn sẽ tìm kiếm dọc từ trái sang phải bằng VLOOKUP. Vì sao ai đó lại sử dụng INDEX và MATCH thay vì VLOOKUP? Bởi vì VLOOKUP chỉ hoạt động khi giá trị tìm kiếm của bạn nằm bên trái giá trị trả về mà bạn muốn. Tuy nhiên, điều này cũng có thể ảnh hưởng tiêu cực đến người dùng muốn một công cụ linh hoạt hơn và trải nghiệm làm việc mượt mà hơn.
Cú pháp của VLOOKUP như sau:
VLOOKUP(lookup_value, table_array, col_index, range_lookup)Đối số cuối cùng là tùy chọn: TRUE (tìm kiếm xấp xỉ) hoặc FALSE (tìm kiếm chính xác). Nếu chúng ta làm ngược lại và tìm kiếm một giá trị trong cột thứ tư và trả về giá trị tương ứng trong cột thứ hai, chúng ta sẽ không nhận được kết quả mong muốn và thậm chí có thể nhận được lỗi.
Theo Microsoft, để VLOOKUP hoạt động đúng, giá trị TÌM KIẾM luôn phải ở cột đầu tiên của phạm vi. Ví dụ, nếu giá trị TÌM KIẾM của bạn nằm trong ô C2, phạm vi của bạn phải bắt đầu từ C.
Mặt khác, để so sánh hai hàm đã cho, công thức INDEX và MATCH bao phủ cả phạm vi ô hoặc bảng, làm cho nó trở thành một phương pháp tìm kiếm mạnh mẽ hơn ngay cả khi công thức phức tạp và khó hiểu hơn.
Công thức XLOOKUP
Cuối cùng, có hàm XLOOKUP.
XLOOKUP là một hàm tìm kiếm Excel xuất hiện sau VLOOKUP và đối tác của nó là HLOOKUP. Nó đã không có sẵn trong Sheets cho đến gần đây. XLOOKUP khác với VLOOKUP trong việc nó hoạt động bất kể nơi mà giá trị tìm kiếm và giá trị trả về nằm trong phạm vi ô hoặc bảng của bạn.
Cú pháp của XLOOKUP như sau:
XLOOKUP(lookup_value, search_array, return_array, not_found, match_mode, search_mode)Ba đối số đầu tiên là bắt buộc và tương tự như VLOOKUP. XLOOKUP cung cấp ba đối số tùy chọn ở cuối để cung cấp kết quả văn bản nếu giá trị không được tìm thấy, chế độ khớp cho kiểu tìm kiếm và chế độ cho việc thực hiện tìm kiếm.
Với những điều này trong tâm trí, bạn có thể thấy rằng XLOOKUP là một tùy chọn tốt hơn VLOOKUP chỉ bởi vì bạn có thể tổ chức dữ liệu của mình theo cách bạn muốn và vẫn có được kết quả mong muốn.
Dù có một số tiện ích khác mà bạn có thể tìm thấy trên Google, những cái này đã được chứng minh là hiệu quả nhất. Một cái có vẻ tốt hơn cái còn lại, nhưng tất cả đều hoạt động rất tốt theo cách của chúng.
Bên cạnh những phương pháp được đề cập ở trên, bạn cũng có thể sử dụng HLOOKUP và các công thức tìm kiếm khác. Để biết thêm thông tin về các công thức này, hãy xem các bài viết khác của chúng tôi, nơi chúng tôi mô tả chúng chi tiết.
Câu hỏi thường gặp
VLOOKUP có tốt hơn INDEX MATCH không?
INDEX MATCH thường được coi là lựa chọn tốt nhất. Nó không bao giờ chậm hơn VLOOKUP. Nó trả về một tham chiếu thay vì giá trị, cho phép chúng ta sử dụng nó cho mục đích đa dạng. Nó không quan trọng phạm vi kết quả so với phạm vi tìm kiếm.
Tuy nhiên, một số người cho rằng công thức INDEX MATCH cho Google Sheets khó hiểu và khó kiểm soát, ngay cả khi nó nhanh hơn và linh hoạt hơn rất nhiều.
Bạn có thể khớp 2 INDEX không?
Các công thức MATCH INDEX trong Google Sheets thường được cấu hình với hàm MATCH được thiết lập để duyệt qua một phạm vi của một cột và cung cấp một kết quả dựa trên các tiêu chí cụ thể đã được chỉ định. Không có cách nào cung cấp nhiều hơn một tiêu chí mà không ghép các giá trị vào một cột trợ giúp hoặc trong công thức chính nó.
Công thức này vượt qua giới hạn này bằng cách sử dụng logic Boolean để tạo một mảng gồm các số một và số không đại diện cho các hàng đáp ứng ba tiêu chí, sau đó sử dụng MATCH để tìm thấy hàng đầu tiên mà nó tìm thấy.
Có cách nào tốt hơn INDEX MATCH không?
Đối thủ mạnh nhất của công thức MATCH INDEX trong Google Sheets là XLOOKUP. XLOOKUP có thể thực hiện các tìm kiếm tương tự, nhưng nó dễ hiểu hơn rất nhiều.
Kết luận
Để tóm lại những gì chúng tôi đã học trong hướng dẫn này về INDEX MATCH trong Google Sheets, có nhiều hàm tìm kiếm trong Google Sheets. INDEX MATCH đã được chứng minh là một trong những công cụ hiệu quả nhất. Tuy nhiên, nên tìm hiểu thêm về việc sử dụng XLOOKUP, VLOOKUP và HLOOKUP cũng.
Tiếp tục khám phá công cụ bảng tính của bạn với Crawlan.com và khám phá tất cả các khả năng mà Google Sheets có thể mang lại! Tiếp tục khám phá tại đây.
