Hàm INDEX kết hợp hàm match có hay hơn VLOOKUP. Cú pháp, ví dụ minh họa, bài tập và cách vận dụng hàm linh hoạt trong excel,…

Các chủ đề trên sẽ được Trường hướng dẫn một cách nhanh chóng và dễ hiểu ở dưới đây bạn nhé.

Cam kết 100% các bạn đọc kỹ bài viết này sẽ hiểu và vận dùng hàm này một cách dễ dàng.

Now, let’s go!

1. ĐỊNH NGHĨA VÀ CÚ PHÁP HÀM INDEX

1.1. HÀM INDEX LÀ GÌ?

Index là hàm excel trả về giá trị hoặc tham chiếu của 01 ô trong excel là giao nhau giữa dòng và cột được xác định bởi các thông số cho trước như:

– Điểm tham chiếu (Tính từ địa chỉ nào)

– Số lượng dòng

– Số lượng cột

– Vùng ô

Lưu ý:

Bạn có thể coi hàm này là Hàm tìm kiếm trong excel.

Bạn có thể dùng để tìm ngược hay tìm xuôi một cách dễ dàng. Hơn hẳn hàm VLOOKUP chỉ tìm được theo chiều từ trái qua phải.

Hàm INDEX có 2 dạng:

• Một là Kết quả của hàm INDEX sẽ trả về giá trị của một ô (Khi bạn đã cho các giá trị tham chiếu kèm theo)
• Hai là trả về một dải ô tham chiếu, được quy định bởi  [area_num]

1.2. CÚ PHÁP HÀM INDEX

Hàm này có 2 cú pháp để bạn áp dụng (áp dụng cái nào tùy vào hoàn cảnh bạn nhé)

Khi bạn tìm một giá trị theo 1 điều kiện thì chọn phương án 1

Thường thì sử dụng công thức thứ 2, bạn sử dụng hàm mảng hoặc kết hợp với các hàm như Sum, sumif…

TRONG ĐÓ:

– Array: Mảng hoặc có thể hiểu là vùng dữ liệu

– Row_num: Số lượng dòng tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm

– Column_num: Số lượng cột tính từ cột đầu tiên của Array đến cột chứa trị cần tìm kiếm

– Area_num: Chọn 1 vùng dữ liệu để tham chiếu tại đó trả về sự giao nhau giữa dòng và cột

Row_num và Column_num giao nhau sẽ xác định được ô cần tìm là ô nào đúng không các bạn.

2. VÍ DỤ MINH HỌA CÁCH SỬ DỤNG HÀM INDEX TRONG EXCEL (CƠ BẢN)

Cho bảng dữ liệu như ảnh dưới.

Yêu cầu: tìm doanh số của cửa hàng có vị trí thứ 3 (Cửa hàng C) tính từ cửa hàng đầu tiên trong danh sách

Giả định đã biết trước vị trí cửa hàng C trong bảng thứ tự số 3 từ trên xuống.

(Để biết được cửa hàng đó có vị trí thứ 3 bạn cần học về hàm MATCH: Xem ngay)

Cú pháp hàm trong trường hợp này như sau:

=INDEX($B$2:$C$7,3,2)

Trong đó:

– $B$2:$C$7 là Array/ Vùng chứa giá trị muốn tìm

– 3 – Số thứ tự dòng của cửa hàng mình muốn tìm doanh số tính từ cửa hàng đầu tiên trong vùng (Dịch đi 3 dòng tính cả B2 =B2 -> B3 ->B4)

– 2 – Số thứ tự cột chứa doanh số tính từ cột đầu tiên của vùng – array (Từ cột B là cột thứ nhất, tính đến cột thứ 2 = cột C

=> Kết quả trả về C4 = 1,300

Như vậy thông qua ví dụ đơn giản này, chúng ta có thể kết luận:

Hàm INDEX có chức năng như một hàm tìm kiếm nâng cao trong excel.

Ở phần dưới Trường sẽ có ví dụ sâu hơn và phân tích mạnh yếu để bạn hiểu thật sâu hàm này.

3. VẬN DỤNG HÀM INDEX KẾT HỢP HÀM MATCH TRONG EXCEL (NÂNG CAO)

Một so sánh nhanh về 3 hàm quan trọng: VLOOKUP, MATCH, INDEX, để dễ hình dung trước khi bắt đầu:

VLOOKUP	MATCH	INDEX
Hàm VLOOKUP tìm kiếm trong bảng với điều kiện tìm kiếm tương ứng. Xem ngay: Hàm VLOOKUP	Trong khi đó hàm MATCH được coi là hàm tìm kiếm vị trí khi cho thông số điều kiện tìm kiếm, cột/Dòng/ Mảng chứa dữ liệu tìm kiếm => từ đó trả về vị trí của dữ liệu trong cột/Dòng/Mảng	Trong khi đó, hàm INDEX trả về giá trị của một ô nếu có số tham chiếu dòng và cột. Xem ví dụ bên dưới

Trở lại câu hỏi ở trên: Làm thế nào để biết được cửa hàng C có số thứ tự dòng thứ 3 tính từ dòng đầu tiên của vùng/ Array.

Câu trả lời: Dùng hàm MATCH để tìm số thứ tự dòng bạn ạ. Xem ngay hàm MATCH

Hàm MATCH sẽ đóng vai trò tìm ra số thứ tự dòng (hoặc cột) mà giá trị đó đang đứng => Sau đó, chúng ta tham chiếu sang giải ô có độ dài tương ứng. Excel sẽ tìm đến vị trí MATCH đã tham chiếu để tìm ra giá trị mà INDEX trả về.

VÍ DỤ CÁCH KẾT HỢP HÀM INDEX VÀ MATCH

Ví dụ sau đây sẽ giải thích rõ cách Hàm INDEX kết hợp hàm MATCH trong excel như thế nào.

Yêu cầu:

• Tìm doanh số của cửa hàng D
• Tìm tên cửa hàng khi biết trước doanh số

3.1. YÊU CẦU SỐ 1: TÌM DOANH SỐ CỦA CỬA HÀNG D

Bạn có thể dùng hàm VLOOKUP để tìm nếu gặp yêu cầu này đúng không nào.

Nhưng ta đã biết hàm INDEX rồi thì hãy tận dụng nó nhé.

Cú pháp hàm INDEX kết hợp hàm MATCH để giải quyết yêu cầu này như sau:

=INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2)

Trong đó:

– $B$2:$C$7: Array/ vùng dữ liệu

– MATCH(C9,$B$2:$B$7,0): Kết quả của hàm này sẽ trả về số thứ tự dòng của cửa hàng D trong Array

– 2: Là cột thứ 2 tính từ cột đầu tiên mà cột đó chứa giá trị tìm kiếm = Doanh số

Kết quả ta nhận được như sau:

 

Bạn hãy tải file về, thay đổi giá trị trong ô C9, kết quả trong ô C10 sẽ tự động đổi theo đó bạn.

Hoặc bạn có thể thay đổi tương ứng doanh thu bạn sẽ thấy kết quả thay đổi theo.

3.2. YÊU CẦU SỐ 2: TÌM TÊN CỬA HÀNG KHI BIẾT TRƯỚC DOANH SỐ

Sử dụng hàm VLOOKUP để tìm ngược từ phải qua trái là một thách thức thực sự với nhiều người học excel.

Nhưng khi dùng hàm INDEX thì quá dễ dàng bạn ạ.

Phương án xử lý trong trường hợp này như sau:

• Tìm ra vị trí của Doanh số biết trước trong cột doanh số
• Tìm trong cột tên cửa hàng: cửa hàng đang có doanh số tương ứng

Trong đó:

Kết hợp lại:

Cụ thể cách sử dụng hàm có trong hình sau:

4. HÀM INDEX KẾT HỢP HÀM MATCH SO VỚI HÀM VLOOKUP

Cái nào mạnh hơn, hay hơn

Trong excel, để biết được hàm nào mạnh hơn thì phải xem trình độ của người dùng ở mức nào.

Nếu ở mức cơ bản thì hàm VLOOKUP hay hơn vì nó phổ biến hơn. Hàm VLOOKUP là một trong những hàm bạn phải học khi dùng excel, còn INDEX thì không.

Nếu ở mức độ cao hơn thì tùy vào sở thích mỗi người.

Nhưng có một số điểm bạn có thể tham khảo để xem lựa chọn hàm nào nhiều hơn.

• VLOOKUP chỉ tìm được khi cột chứa dữ liệu tìm kiếm đứng đầu bảng
• VLOOKUP chỉ tìm được từ trái qua phải
• INDEX không phân biệt cột chứa dữ liệu là cột nào, chỉ cần tìm ra vị trí
• Do đó INDEX đa di năng hơn
• INDEX dùng sẽ giảm bớt độ rộng vùng phải quét => Dung lượng file tối ưu hơn

 

5. INDEX KẾT HỢP CÙNG HÀM SUM

Bài toán vẫn cũ, nhưng đổi yêu cầu:

Tính doanh số cột C:

Tính doanh số 3 cửa hàng trên cùng

6. INDEX KẾT HỢP CÙNG HÀM MIN/ MAX

Bài toán vẫn cũ, nhưng đổi yêu cầu khi kết hợp MIN/ MAX

Tìm cửa hàng doanh số thấp nhất:

Tìm cửa hàng doanh số cao nhất:

7. INDEX KẾT HỢP CÙNG NHÓM HÀM AVERAGE

Bài toán vẫn cũ, nhưng đổi yêu cầu:

Tính trung bình cộng doanh số 4 cửa hàng từ số 2 đến số 5

Tính trung bình cộng doanh số 3 cửa hàng đầu: 

8. DOWNLOAD FILE MẪU VÀ BÀI TẬP THỰC HÀNH

Hãy download ngay file dưới đây nếu:

• Các bạn muốn có được file mẫu cho các ví dụ phía trên.
• Muốn thực hành để thành thạo hàm này hơn nữa.

Click link dưới đây để download bài tập bạn nhé.

Lưu ý:

• Dấu [] xuất hiện ngoài [Column_num] và [Area_num] sẽ tương đương với việc bạn không cần có tham số đó trong cú pháp hàm. Khi không nhập tham số đó, excel sẽ tự động lấy giá trị là 1 khi tính toán.
• Cách áp dụng thứ 2, đặc biệt là Area_num rất khó hiểu đúng không bạn. Trường cũng hiếm khi dùng nó lắm, nên bạn có thể bỏ qua.
• Cách thứ nhất, Trường cũng hay dùng chính xác cho từng cột thay vì phải nhập cả Column_num

 

Cảm ơn bạn đã dành thời gian để theo dõi phần hướng dẫn của Trường.

Rất hi vọng bạn sẽ luôn ủng hộ Trường và Blog chuyên về Excel.

Truongpx – Admin Hệ thống các website chuyên về kỹ năng làm việc.