Series: “Làm trùm” VLOOKUP – Phần 9: Liệt kê giá trị thứ n thỏa điều kiện

Home / Excel / Series: “Làm trùm” VLOOKUP – Phần 9: Liệt kê giá trị thứ n thỏa điều kiện
This entry is part 9 of 10 in the series "Làm trùm" VLOOKUP

Như chúng ta đã biết, mặc định, VLOOKUP sẽ trả ra giá trị đầu tiên thỏa điều kiện nếu trong bảng tham chiếu có nhiều hơn 1 giá trị. Hãy cùng Coffee Excel tìm hiểu cách trả về giá trị thứ 2, thứ 3 hoặc thứ n thỏa điều kiện qua Phần 9: Liệt kê giá trị thứ n thỏa điều kiện của Series: “Làm trùm” VLOOKUP nhé.

Đọc các phần trước của Series: “Làm trùm” VLOOKUP:

• Phần 1: Cơ bản về VLOOKUP

• Phần 2: Thông số [range_lookup] dùng để làm gì?

• Phần 3: Sử dụng ký tự đại diện trong VLOOKUP

• Phần 4: Thay đổi số thứ tự cột tự động trong hàm VLOOKUP

• Phần 5: Tự mở rộng vùng dữ liệu VLOOKUP khi thêm dữ liệu trong bảng

• Phần 6: VLOOKUP từ phải sang trái

• Phần 7: VLOOKUP nhiều điều kiện

• Phần 8: VLOOKUP cả dòng và cột

Liệt kê giá trị thứ n thỏa điều kiện

Chúng ta cùng xem ví dụ ở bảng dữ liệu sau đây:

Liệt kê giá trị thứ n thỏa điều kiện - Data Table

Chúng ta thấy, ở ô D6 sẽ trả về giá trị của cột Age ứng với Employee Dave thứ 3 trong bảng dữ liệu của chúng ta (dòng 8)

Để trả về giá trị như mong muốn, chúng ta sử dụng hàm sau đây:

{= VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},ROW(A2:A8),B2:B8),2,0)}

Chúng ta cần lưu ý 2 điều:
1. Đây là công thức mảng nên chúng ta sẽ không gõ 2 đấu {} ở đầu và cuối hàm mà sử dụng tổ hợp phím CTRL + SHIFT + ENTER khi viết hàm xong.

2. Với các vùng dữ liệu trong hàm như A2:A8, khi viết hàm, chúng ta nên cố định (địa chỉ tuyệt đối) để không bị lỗi khi copy. Do ở đây chỉ có 1 ô dữ liệu nên Coffee Excel viết hàm bằng địa chỉ tương đối cho dễ nhìn.

Cách thức hoạt động của hàm trên

Để hiểu cách thức hoạt động của hàm trên, chúng ta cùng tìm hiểu ý nghĩa của các hàm lồng bên trong là hàm IF, hàm SMALL và hàm CHOOSE

Hàm IF

{= VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},ROW(A2:A8),B2:B8),2,0)}

Các ký tự màu đỏ trong hàm là những ký tự bao gồm trong hàm IF. Đây cũng là một hàm IF bình thường với cú pháp IF(logical_test, [value_if_true], [value_if_false]), điểm khác biệt ở đây là hàm IF đang được viết dưới dạng công thức mảng. Và hàm IF có ý nghĩa là trong mảng A2:A8. Nếu giá trị bằng với ô E3 (Dave) thì sẽ trả về số thứ tự của hàng đó, nếu không sẽ trả về là FALSE.

Như vậy, với hàm IF bên trên, kết quả của hàm là một mảng có 7 giá trị, mỗi giá trị ứng với điều kiện trên. Như vậy, giá trị mảng:

{A2:A8} = {John, Dave, Dave, Chris, Jack, Thomas, Dave}

sẽ trả về kết quả của hàm IF theo công thức mảng là mảng:

{FALSE, 3, 4, FALSE, FALSE, FALSE, FALSE, 8}

Hàm SMALL

{= VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},ROW(A2:A8),B2:B8),2,0)}

Như chúng ta thấy, hàm IF mà chúng ta phân tích khi nãy nằm trong hàm SMALL. Vậy để phân tích hàm SMALL, chúng ta có thể viết ngắn gọn thành như sau:

SMALL({FALSE, 3, 4, FALSE, FALSE, FALSE, FALSE, 8},E4)

Trước hết, chúng ta cùng hiểu ý nghĩa của hàm SMALL. Hàm SMALL có cú pháp là: =SMALL(array,k). Hàm SMALL sẽ trả về giá trị nhỏ thứ k trong bảng/mảng/chuỗi array. Ví như như: =SMALL({1,2,3,4,5,6,7,8}, 3) sẽ trả về giá trị là 3.

Với hàm bên trên, kết quả của hàm SMALL sẽ là 8 (giá trị nhỏ thứ 3 trong mảng bên trên, FALSE không tính vào)

Kết hợp IF, SMALL, CHOOSE và VLOOKUP

Như vậy, 2 hàm trên, look_up­­­_value­ trong hàm VLOOKUP sẽ là 8, tức là số dòng của bảng. Như vậy, chúng ta cần tạo ra 1 bảng có 2 cột, một cột là số thứ tự của dòng, 1 cột là giá trị của cột Age. Đại loại như thế này:

Số thứ tự cộtAge
219
320
423
524
626
732
847

 

Để làm được điều này, chúng ta sử dụng hàm CHOOSE, các bạn có thể xem ở Phần 6: VLOOKUP từ phải sang trái để hiểu rõ hơn về hàm CHOOSE nhé.

Như trong phần 6, chúng ta có thể định nghĩa bảng này bằng hàm CHOOSE bằng hàm:

CHOOSE({1,2},ROW(A2:A8),B2:B8)

Bảng này sẽ có 2 cột, cột 1 là số thứ tự của dòng từ A2 tới A8, cột 2 là giá trị của cột Age.

Kết hợp lại các hàm ở trên, chúng ta có công thức để liệt kê giá trị thứ n thỏa điều kiện bằng hàm sau:

{= VLOOKUP(SMALL(IF(A2:A8=E3,ROW(A2:A8)),E4),CHOOSE({1,2},ROW(A2:A8),B2:B8),2,0)}

Kết quả

Với hàm trên, chúng ta có hàm để liệt kê giá trị thứ n thỏa điều kiện như bảng sau:

Liệt kê giá trị thứ n thỏa điều kiện - Kết quả

Điểm yếu của hàm này là khá nặng do kết hợp giữ VLOOKUP và công thức mảng (2 mảng), nhưng mà thôi, có còn hơn không nha cả nhà.

Như vậy, qua bài viết trên, chúng ta đã biết cách Liệt kê giá trị thứ n thỏa điều kiện. Hãy cùng đón đọc Phần 10: Liệt kê tất cả giá trị thỏa điều kiện của Series: “Làm trùm” VLOOKUP nhé cả nhà.

Nguồn: Excel off the Grid

Dịch và Hiệu đính: Coffee Excel

Series Navigation<< Series: “Làm trùm” VLOOKUP – Phần 8: VLOOKUP cả dòng và cộtSeries: “Làm trùm” VLOOKUP – Phần 10: Liệt kê toàn bộ giá trị thỏa điều kiện >>
Series: "Làm trùm" VLOOKUP - Phần 8: VLOOKUP cả dòng và cột
Series: “Làm trùm” VLOOKUP - Phần 10: Liệt kê toàn bộ giá trị thỏa điều kiện

Professionals in Finance, Accounting, Auditing. A financial, accounting, auditing Excel lovers.

Read more:
Sổ tay thuế Việt Nam
Sổ tay thuế Việt Nam 2018 – từ PwC Việt Nam

Cứ mỗi năm, PwC Việt Nam lại xuất bản một ấn phẩm Sổ tay thuếViệt Nam, cập nhận những thay...

Close