hàm index và match là công cụ phổ biến nhất trong excel để thực hiện các tra cứu nâng cao hơn. do index và match cực kỳ linh hoạt, bạn có thể thực hiện tra cứu theo chiều ngang và dọc, tra cứu 2 chiều, tra cứu phía bên trái, tra cứu phân biệt chữ hoa hay chữ thường và thậm chí là tra cứu dựa trên nhiều tiêu chí khác. nếu bạn muốn cải thiện kỹ năng excel của mình, bài viết về hàm index nâng cao và match nâng cao này sẽ giúp bạn tìm hiểu và thực hành cách ứng dụng chúng trong công việc hàng ngày của bạn. cùng tìm hiểu nhé!

xem thêm: hướng dẫn cách viết các hàm trong excel chi tiết dễ hiểu nhất

Bạn đang xem: CÁCH ỨNG DỤNG HÀM INDEX VÀ MATCH NÂNG CAO (PHẦN 1)

những điều chủ công về hàm index và match

trong bài viết này, những kiến thức chủ yếu về cú pháp và cách sử dụng của từng hàm sẽ được giới thiệu khái quát mà không đi sâu hơn, do bài viết tập trung phân tích khía cạnh ưu việt của việc kết hợp hàm index nâng cao và match để tìm kiếm thay thế trong excel thay cho hàm vlookup.

hàm index nâng cao

tính năng: hàm index trả về giá trị của một ô trong bảng dựa trên số cột và hàng.

công thức hàm:

=index(array, row_num, [column_num])

trong đó,

  • array: một dải ô mà bạn muốn trả về một giá trị
  • row_num: số thứ tự của hàng trong dải ô (array) mà bạn muốn trả về một giá trị. nếu tham số này được bỏ qua, thì column_num là giá trị bắt buộc.
  • colum_num: số thứ tự của cột trong dải ô (array) mà bạn muốn giá trị được trả về. nếu  tham số này được bỏ qua, thì row_num là là giá trị bắt buộc.

lưu ý: nếu cả 2 tham số row_num và colum_num đều được sử dụng, thì hàm index sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác định.

ví dụ minh họa: hãy cùng xem ví dụ dưới đây để hiểu rõ hơn cách sử dụng hàm này nhé.

giả sử bạn nhập công thức hàm như sau: =index(b4:f12,1,2)

nghĩa là, công thức tìm kiếm dải ô b4:f12 rồi trả về giá trị của ô ở hàng thứ nhất (1) và cột thứ hai (2), cụ thể là ô c4.

cÁch Ứng dỤng hÀm index vÀ match nÂng cao (phẦn 1)

khá đơn giản, phải không? tuy nhiên, trong thực tế, bạn không biết mình cần trả về giá trị ở hàng nào và cột nào. do đó, hàm match là lựa chọn kết hợp tuyệt vời.

xem thêm: hướng dẫn sử dụng hàm match và ứng dụng hay của hàm match trong excel các phiên bản

hàm match

tính năng: hàm match tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.

Xem thêm: Cách bỏ dấu cách xuống dòng Alt enter trong chuỗi ký tự – Học Excel Online Miễn Phí

công thức hàm:

=match(lookup_value, lookup_array, [match_type])

trong đó,

  • lookup_value: chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị logic.
  • lookup_array: dải ô được tìm kiếm
  • match_type: thông số này nói cho hàm match biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối. với:

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

nếu là 0: tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. trong sự kết hợp của hàm index nâng cao và match, hầu như bạn luôn cần một kết hợp tuyệt đối, vì vậy bạn đặt đối số thứ ba của hàm match thành 0.

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

lưu ý: lúc đầu, tính hữu ích của hàm match có thể không rõ ràng. Điều ta thật sự muốn biết đó chính là giá trị ô chứ không phải vị trí của giá trị trong dải ô.

Điều bạn cần ghi nhớ chính là vị trí tương đối của giá trị cần tìm (cụ thể là số thứ tự của cột hay/và hàng) chính là tất cả những gì bạn cần để nhập vào câu lệnh row_num hay/và column_num của hàm index. như đã giới thiệu ở trên, hàm index có thể trả về giá trị ở giao điểm của hàng và cột xác định, nhưng nó không thể xác định một cách chính xác bạn muốn cột nào hàng nào.

ví dụ minh họa: hãy cùng xem ví dụ dưới đây để hiểu rõ hơn cách sử dụng hàm này nhé.

giả sử nếu dải ô b1:b3 chứa các giá trị “new-york”, “paris”, “london”, và bạn sử dụng công thức

=match(“london”,b1:b3,0)

nghĩa là, công thức sẽ trả kết quả về số 3 bởi vì “london” là mục thứ ba trong dải ô (array) tra cứu.

cÁch Ứng dỤng hÀm index vÀ match nÂng cao (phẦn 1)

cách sử dụng hàm index nâng cao và hàm match trong excel

qua các kiến thức và ví dụ chủ yếu trên, bạn đọc đã có thể biết các những hàm này hoạt động như thế nào phải không? tóm lại, hàm index tìm giá trị tra cứu theo số cột và hàng, và hàm match cung cấp các số đó. vậy nếu kết hợp 2 hàm này lại thì sao, hãy cùng xem ví dụ dưới đây nhé.

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

=index (column to return a value from, match (lookup value, column to lookup against, 0))

ví dụ minh họa:

giả sử bạn có một danh sách dữ liệu và bạn cần tra cứu dân số của nhật bản:

cÁch Ứng dỤng hÀm index vÀ match nÂng cao (phẦn 1)

bạn sử dụng công thức:

=index($d$2:$d$10,match(“nhật bản”,$b$2:$b$10,0))

trong đó,

  • hàm match tìm kiếm giá trị cần tìm “nhật bản” ở cột b, chính xác hơn là dải ô b2:b10,  rồi trả về số 3, bởi vì “nhật bản” nằm thứ ba trong danh sách
  • hàm index lấy số 3 từ thông số thứ hai (row_num), thông số này chỉ rõ bạn muốn trả về giá trị từ hàng nào, rồi trở thành công thức đơn giản như sau =index($d$2:$d$10,3).

Đơn giản có thể nói, công thức được đọc như sau: tìm trong dải ô d2:d10 rồi trả về giá trị của ô ở hàng thứ 3, cụ thể là ô d4, bởi vì chúng ta bắt đầu đếm từ hàng thứ 2.

Xem thêm: Hướng dẫn dùng hàm YEAR và Ví dụ cụ thể dễ hiểu

và bạn sẽ có được kết quả hiển thị như hình trên.

lưu ý: số hàng và số cột trong mảng (array) của hàm index nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm match. nếu không thì, công thức sẽ trả về kết quả không chính xác.

tại sao không sử dụng hàm vlookup?

nếu bạn sử dụng công thức sau: =vlookup(“nhật bản”,$b$2:$d$2,3)

thì bạn cũng có được kết quả tương tự, thế nhưng, ở ví dụ này, gitiho muốn diễn giải cho bạn thấy cách kết hợp hàm index và match mà thôi. các ví dụ khác bên dưới sẽ cho bạn thấy khả năng thật sự của sự kết hợp hàm index và match – điều này giúp bạn dễ dàng xử lý nhiều viễn cảnh phức tạp khi hàm vlookup không xử lý được. cùng đón đọc ở phần 2 của bài viết:

tuyệt đỉnh excel – trở thành bậc thầy excel trong 16 giờ

tổng kết

qua bài viết này, gitiho hy vọng bạn đọc có thể hiểu được cách sử dụng căn bản và kết hợp của hàm index nâng cao và match. ngoài ra, để thành thạo các thủ thuật của hàm ứng dụng vào công việc hằng ngày bạn nên tham khảo các hàm vlookup, if, and và luyện tập các bài tập excel có đáp án được cung cấp.

chúc bạn học tốt!

bài viết đọc thêm khác:

hướng dẫn cách dùng hàm index trong excel chi tiết nhất

hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong excel

hướng dẫn cách sắp xếp ngày trong excel

hướng dẫn cách dùng hàm vlookup qua các ví dụ từ chủ công đến nâng cao

hướng dẫn cách sử dụng hàm if với nhiều điều kiện: and, or, hàm if lồng nhau và hơn thế

gitiho.com – nền tảng học online duy nhất tại việt nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

với sứ mệnh: “mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển gitiho.com đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ chủ yếu đến chuyên sâu xung quanh các lĩnh vực: tin học văn phòng, phân tích dữ liệu, thiết kế, công nghệ thông tin, kinh doanh, marketing, quản lý dự án…

gitiho tự hào khi được đồng hành cùng:

  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: vietinbank, vietcombank, bidv, vp bank, th true milk, vnpt, fpt software, samsung sdiv, ajinomoto việt nam, messer,…
  • 100.000+ học viên trên khắp việt nam

nếu bạn quan tâm đến các khóa tin học văn phòng của gitiho, với mong muốn giỏi tin học để tăng năng suất làm việc lên gấp 5 lần, hãy truy cập ngay tẠi ĐÂy để tìm cho mình khóa học phù hợp nhé!

cÁch Ứng dỤng hÀm index vÀ match nÂng cao (phẦn 1)


Nguồn: https://ngoalongtamquoc.vn
Danh mục: EXCEL