Thủ thuật

Hướng dẫn cách kết hợp hàm MATCH với OFFSET trong Google Sheet chi tiết vô cùng đơn giản

Trương Nguyễn Anh Thư

09-05-2023, 12:14 pm

Trong Google Sheet có nhiều công thức hàm, các công thức này thường hỗ trợ cho nhau để giúp chúng ta tính toán nhanh chóng hơn. Vậy khi hàm MATCH khi được kết hợp với hàm OFFSET sẽ như thế nào? Hãy cùng chúng mình tìm hiểu qua bài viết dưới đây nhé!

Cách áp dụng hàm MATCH vào trong Google Sheet

MATCH là hàm gì?

Hàm MATCH trong Google Sheet là một hàm tìm kiếm một giá trị xác định trước trong một phạm vi ô rồi sau đó trả về đúng vị trí tương đối của giá trị nằm trong phạm vi đó.

Cách áp dụng hàm MATCH vào trong Google Sheet

Công thức áp dụng hàm MATCH:

= MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm)

Trong đó:

+ khóa_tìm_kiếm: Biểu thị giá trị mà bạn đang muốn tìm kiếm.

+ dải_ô: Biểu thị vùng dữ liệu mà bạn muốn xác định vị trí của khóa_tìm_kiếm. Là dải ô mà bạn chọn chỉ có thể có duy nhất một hàng hoặc là 1 cột. Ví dụ như: A1:A10 hoặc A1:H1.

+ [loại_tìm_kiếm]: Đây là một phương thức tìm kiếm. Thường sẽ có 3 kiểu tìm kiếm trong Sheet là -1; 0 và 1.

Ví dụ minh họa cho trường hợp áp dụng hàm MATCH :

Cho bảng dữ liệu sau đây bao gồm các trường như: STT, TÊN SP, HÃNG, GIÁ BÁN.

Bạn hãy sử dụng hàm MATCH để xác định vị trí của các sản phẩm.

=MATCH(F2;$B$2:$B$10;0)

Ví dụ mẫu minh họa hàm MATCH như hình trên

Hướng dẫn cách áp dụng hàm OFFSET vào trong Google Sheet

OFFSET là hàm gì?

Hàm OFFSET trong Google Sheet là một hàm trả về giá trị tham chiếu cách một số hàng hoặc một số cột đã được xác định trước. Kết quả trả về của hàm OFFSET này có thể là một ô hoặc một dải ô dựa vào số hàng và số cột mà bạn muốn trả về.

Cách áp dụng hàm OFFSET vào trong Google Sheet

Công thức áp dụng hàm OFFSET:

=OFFSET(tham_chiếu_ô; hàng_bù_trừ; cột_bù_trừ; chiều cao; chiều rộng)

Trong đó:

+ tham_chiếu_ô: Là một ô cố định ban đầu để xác định hàng_bù_trừ và cột_bù_trừ.

+ hàng_bù_trừ: Biểu thị số hàng được dùng để bù trừ.

+ cột_bù_trừ: Biểu thị số cột được dùng để bù trừ.

+ (chiều cao): Thể hiện chiều cao của dải ô trả về, bắt đầu từ đích bù trừ.

+(chiều rộng): Thể hiện chiều rộng của dải ô trả về, bắt đầu từ đích bù trừ.

Ví dụ minh họa cho trường hợp áp dụng hàm OFFSET :

Cho bảng dữ liệu sau đây bao gồm các trường như: STT, TÊN SP, HÃNG, GIÁ BÁN.

Bạn hãy sử dụng hàm OFFSET để xác định hãng của các sản phẩm trên.

=OFFSET(B2;0;1)

Ví dụ minh họa hàm MATCH như hình trên

Cách kết hợp hàm MATCH và hàm OFFSET trong Google Sheet

Công thức của hàm kết hợp:

=OFFSET(tham_chiếu_ô; MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm); cột_bù_trừ; chiều cao; chiều rộng)

Trong đó:

+ tham_chiếu_ô: Ô cố định ban đầu để có thể xác định được hàng_bù_trừ và cột_bù_trừ.

+ MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm)

+ cột_bù_trừ: Biểu thị số cột để bù trừ.

+ chiều cao: Biểu thị chiều cao của dải ô trả về, bắt đầu từ đích bù trừ.

+ chiều rộng: Biểu thị chiều rộng của dải ô trả về, bắt đầu từ đích bù trừ.

Ví dụ minh họa cho hàm kết hợp này:

Cho bảng dữ liệu sau đây có gồm các trường như: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN.

Kết hợp hàm MATCH và hàm OFFSET để có thể tìm ra giá bán của sản phẩm.

Công thức của hàm:

=OFFSET($A$1;MATCH(F2;$B$2:$B$10;0);3)

Giải thích:

+ $A$1: Chỉ ra ô cố định để xác định được hàng và cột bù trừ.

+ MATCH(F2;$B$2:$B$10;0): Giúp xác định số hàng dựa vào vị trí của tên sản phẩm nằm trong cột TÊN SP.

+ 3: Lấy từ ô A1 đến cột D (GIÁ BÁN) là 3.

Kết hợp hàm MATCH và OFFSET như hình trên

Các lỗi bạn sẽ thường gặp phải khi kết hợp hàm MATCH và hàm OFFSET

Lỗi #N/A

Lý do trả ra kết quả lỗi: Lỗi này xảy ra khi bạn nhập thiếu đối số của hàm, hàm không tìm thấy đối số ước tính, không tìm thấy giá trị, hoặc trong hàm MATCH này dải ô phải là hàng đơn hoặc là cột đơn.

Cách khắc phục lỗi này: Bạn tiến hành kiểm tra lại xem các đối số của hàm MATCH và OFFSET đã nhập đủ và đúng hay chưa, liệu dải ô đã chọn trong hàm MATCH có phải hàng đơn hay cột đơn không (ví dụ như: A2:A10, A1:G1).

Lỗi #REF

Lý do trả về kết quả lỗi: Lỗi xảy ra khi đối số hàng hoặc cột bù trừ của hàm OFFSET trả về một giá trị nằm ngoài bảng tính hoặc kết quả trả về bị đè lên một ô khác.

Cách khắc phục lỗi này: để khắc phục lỗi, bạn kiểm tra lại xem các đối số hàng bù trừ và cột bù trừ. Kiểm tra xem kết quả trả về liệu có bị đè lên ô nào thì chúng ta sẽ dịch chuyển công thức hàm đến vị trí khác sao cho phù hợp.

Lỗi #ERROR

Lý do trả về kết quả lỗi: Lỗi #ERROR xuất hiện do bạn đã nhập sai cú pháp hàm.

Cách khắc phục: Bạn tiến hành kiểm tra các đối số và dấu ngăn cách các đối số đã nhập xem chúng có đúng quy cách không.

Lỗi #VALUE

Lý do trả về kết quả lỗi: Do hàm không tìm thấy giá trị mảng, hoặc bạn đã nhập sai kiểu dữ liệu của đối số. Hoặc khi tham số chiều cao và chiều rộng của hàm OFFSET bị nhỏ hơn 1.

Cách khắc phục lỗi: Để có thể khắc phục lỗi này, bạn tiến hành kiểm tra lại kiểu dữ liệu của từng đối số, xem giá trị tìm kiếm có phù hợp với dải ô chứa giá trị tìm kiếm hay không. Và phải đảm bảo rằng tham số chiều cao và chiều rộng của hàm OFFSET này phải lớn hơn hoặc bằng 1.

Một số lưu ý quan trọng khi kết hợp hàm MATCH và hàm OFFSET này

- Hàm MATCH là hàm chỉ trả về vị trí của giá trị đó chứ không trả về giá trị đó.

- Hàm MATCH và hàm OFFSET đều không phân biệt được chữ hoa hay chữ thường khi bạn nhập công thức vào hàm. Ví dụ như: OFFSET=offset.

- Nếu hàm MATCH không tìm thấy được giá trị chính xác nào thì hàm này sẽ trả về lỗi #N/A.

- Đối số thứ 4 và 5 của hàm OFFSET này phải có giá trị lớn hơn hoặc bằng 1. Nếu sai thì hàm này sẽ trả về giá trị #VALUE!.

- Trong hàm OFFSET nếu hàng hoặc cột bù trừ nằm ở ngoài bảng tính thì hàm sẽ trả về lỗi #REF!.

- Trong hàm OFFSET nếu kết quả mảng trả về bị đè lên ô khác thì hàm sẽ trả về lỗi #REF!.

Một số bài tập ví dụ về việc kết hợp hai hàm MATCH và hàm OFFSET trong Google Sheet

Bảng dữ liệu quản lý lương của nhân viên như hình trên

Bài tập 1: Các bạn hãy tìm Tên NV, Chức vụ, Phòng ban theo Mã NV.

- Tiến hành tìm Tên NV theo Mã NV:

=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);1)

Giải thích:

+ $A$2: Chỉ ra ô cố định để xác định hàng và cột bù trừ.

+ MATCH(C16;$A$3:$A$13;0): Giúp bạn xác định số hàng dựa vào vị trí của mã nhân viên trong ở cột MÃ NV.

+ 1: Lấy từ ô A2 đến cột B (TÊN NV) là 1.

- Tiến hành tìm Chức vụ theo Mã NV:

=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);2)

Giải thích:

+ $A$2: Chỉ ra ô dùng cố định được dùng để xác định hàng và cột bù trừ.

+ MATCH(C16;$A$3:$A$13;0): Giúp xác định số hàng dựa vào vị trí của mã nhân viên nằm trong cột MÃ NV.

+ 2: Lấy từ ô A2 đến cột C (CHỨC VỤ) là 2.

- Tiến hành tìm phòng ban theo Mã NV:

=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);3)

Giải thích:

+ $A$2: Chỉ ra ô cố định dùng để xác định hàng và cột bù trừ.

+ MATCH(C16;$A$3:$A$13;0): Giúp xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.

+ 3: Lấy từ ô A2 đến cột D (PHÒNG BAN) là 3.

Tìm Tên NV, Chức vụ, Phòng ban theo Mã NV như hình trên

Bài tập 2: Dựa vào các kiến thức hàm đã học, bạn hãy tìm lương dựa theo tháng của nhân viên.

=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);MATCH(C15;B2:P2;0))

Giải thích:

+ $A$2: Chỉ ra ô cố định được dùng để xác định hàng và cột bù trừ.

+ MATCH(C16;$A$3:$A$13;0): Giúp xác định số hàng dựa vào vị trí của mã nhân viên nằm trong cột MÃ NV.

+ MATCH(C15;B2:P2;0): Giúp xác định số cột dựa vào vị trí của tháng năm nằm trong bảng lương 12 tháng.

Tìm lương theo tháng của nhân viên như hình trên

Bài tập 3: Dựa vào kiến thức đã được học về hàm, bạn hãy tìm lương 12 tháng của một nhân viên.

=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);4;1;12)

Giải thích:

+ $A$2: Biểu thị ô cố định được dùng để xác định hàng và cột bù trừ.

+ MATCH(C16;$A$3:$A$13;0): Giúp xác định số hàng dựa vào vị trí của mã nhân viên nằm trong cột MÃ NV.

+ 4: Lấy từ ô A2 đến vị trí lương tháng đầu tiên (Tháng 01) là 4.

+ 1: Chỉ ra chiều cao của kết quả trả về là 1 tương ứng với một nhân viên.

+ 12: Chỉ ra chiều rộng của kết quả trả về là 12 tương ứng với 12 tháng.

Tìm lương 12 tháng của một nhân viên như hình trên

Những câu hỏi thường gặp trong khi kết hợp hàm MATCH và hàm OFFSET

Hàm MATCH còn có thể kết hợp được với hàm nào khác nữa không?

Ngoài việc kết hợp với hàm OFFSET thì hàm MATCH này còn có thể kết hợp với nhiều hàm khác nhau như hàm INDEX, VLOOKUP, IF, INDIRECT, IMPORTRANGE giúp cho công thức hàm cập nhật một cách tự động khi dữ liệu có sự thay đổi.

Lợi ích của việc kết hợp hàm MATCH với hàm OFFSET trong Google Sheet là gì?

Khi kết hợp hàm MATCH cùng với hàm OFFSET trong Google Sheet sẽ giúp chúng ta tham chiếu ô một cách tự động mà không cần phải sử dụng lặp lại nhiều lần với một công thức hàm OFFSET.

Trên đây là bài viết thông tin đến các bạn cách kết hợp hàm MATCH và hàm OFFSET trong Google Sheet. Mong rằng bài viết này sẽ mang lại những thông tin mà bạn cần. Cảm ơn các bạn đã theo dõi đến đây và chúc các bạn áp dụng thành công!