Cách tạo công thức tra cứu (Lookup) với nhiều tiêu chí trong Excel

Làm theo hướng dẫn

Để làm theo các bước trong hướng dẫn này, hãy nhập dữ liệu mẫu vào các ô sau, như hình ảnh bên dưới. Hàng 3 và 4 được để trống để chứa công thức mảng sẽ tạo ra trong hướng dẫn. (Lưu ý hướng dẫn này không bao gồm định dạng như trong hình.)

• Nhập dữ liệu phạm vi trên vào ô D1 đến F2.
• Nhập phạm vi thứ hai vào ô D5 đến F11.

Tạo hàm INDEX trong Excel

Hàm INDEX là một trong những hàm ít có nhiều dạng trong Excel. Hàm có dạng Mảng (Array Form) và dạng Tham chiếu (Reference Form). Dạng Mảng trả về dữ liệu từ cơ sở dữ liệu hoặc bảng dữ liệu. Dạng Tham chiếu cho tham chiếu ô hoặc vị trí dữ liệu trong bảng.

Trong hướng dẫn này, dùng Dạng Mảng để tìm tên nhà cung cấp cho "widgets titanium", thay vì tham chiếu ô đến nhà cung cấp đó trong cơ sở dữ liệu.

Làm theo các bước sau để tạo hàm INDEX:

1. Chọn ô F3 làm ô hoạt động. Đây là nơi hàm lồng nhau sẽ được nhập.
2. Đi đến tab Công thức (Formulas).
3. Chọn Tra cứu & Tham chiếu (Lookup & Reference) để mở danh sách thả xuống hàm.
4. Chọn INDEX để mở hộp thoại Chọn đối số (Select Arguments).
5. Chọn array, row_num, column_num.
6. Chọn OK để mở hộp thoại Đối số hàm (Function Arguments). Trong Excel cho Mac, trình xây dựng công thức (Formula Builder) sẽ mở ra.
7. Đặt con trỏ vào hộp văn bản Array.
8. Chọn vùng D6 đến F11 trong trang tính để nhập phạm vi vào hộp thoại.

Lưu ý quan trọng: Giữ hộp thoại Đối số hàm mở. Công thức chưa xong. Bạn sẽ hoàn tất ở các bước dưới.

Bắt đầu hàm MATCH lồng nhau

Khi lồng hàm này vào hàm khác, không thể mở trình xây dựng công thức của hàm lồng để nhập đối số. Hàm lồng phải nhập như một đối số của hàm đầu tiên.

Khi nhập hàm thủ công, các đối số của hàm được tách nhau bằng dấu phẩy.

Bước đầu để nhập hàm MATCH lồng là nhập đối số Lookup_value. Lookup_value là vị trí hoặc tham chiếu ô cho từ khóa tìm kiếm cần khớp trong cơ sở dữ liệu.

Lookup_value chỉ chấp nhận một tiêu chí tìm kiếm. Để tìm nhiều tiêu chí, mở rộng Lookup_value bằng cách nối (concatenate) hai hoặc nhiều tham chiếu ô bằng ký hiệu & (dấu &).

1. Trong hộp thoại Đối số hàm, đặt con trỏ vào hộp văn bản Row_num.
2. Nhập MATCH(.
3. Chọn ô D3 để nhập tham chiếu ô đó vào hộp thoại.
4. Nhập & (dấu &) sau tham chiếu ô D3 để thêm tham chiếu ô thứ hai.
5. Chọn ô E3 để nhập tham chiếu ô thứ hai.
6. Nhập , (dấu phẩy) sau tham chiếu ô E3 để hoàn tất nhập đối số Lookup_value của hàm MATCH.

Ở bước cuối cùng của hướng dẫn, các Lookup_value sẽ được nhập vào ô D3 và E3 của trang tính.

Hoàn tất hàm MATCH lồng nhau

Bước này thêm đối số Lookup_array cho hàm MATCH lồng. Lookup_array là phạm vi ô mà hàm MATCH tìm kiếm để tìm đối số Lookup_value đã thêm ở bước trước.

Vì hai trường tìm kiếm đã xác định trong đối số Lookup_value, ta phải làm tương tự cho Lookup_array. Hàm MATCH chỉ tìm một mảng cho mỗi thuật ngữ. Để nhập nhiều mảng, dùng dấu & để nối các mảng lại.

1. Đặt con trỏ ở cuối dữ liệu trong hộp văn bản Row_num. Con trỏ xuất hiện sau dấu phẩy ở cuối mục nhập hiện tại.
2. Chọn vùng D6 đến D11 trong trang tính để nhập phạm vi. Đây là mảng đầu tiên hàm tìm kiếm.
3. Nhập & (dấu &) sau tham chiếu ô D6:D11. Ký hiệu này khiến hàm tìm kiếm hai mảng.
4. Chọn vùng E6 đến E11 trong trang tính để nhập phạm vi. Đây là mảng thứ hai hàm tìm kiếm.
5. Nhập , (dấu phẩy) sau tham chiếu ô E3 để hoàn tất nhập đối số Lookup_array của hàm MATCH.

Giữ hộp thoại mở cho bước tiếp theo.

Thêm đối số Match_type

Đối số thứ ba và cuối cùng của hàm MATCH là đối số Match_type. Đối số này nói cho Excel cách khớp Lookup_value với giá trị trong Lookup_array. Các lựa chọn có sẵn là 1, 0, hoặc -1.

Đối số này là tùy chọn. Nếu bỏ qua, hàm dùng giá trị mặc định là 1.

• Nếu Match_type = 1 hoặc bỏ qua: MATCH tìm giá trị lớn nhất nhỏ hơn hoặc bằng Lookup_value. Dữ liệu Lookup_array phải sắp xếp tăng dần.
• Nếu Match_type = 0: MATCH tìm giá trị bằng chính xác Lookup_value. Dữ liệu Lookup_array có thể sắp xếp bất kỳ thứ tự nào.
• Nếu Match_type = -1: MATCH tìm giá trị nhỏ nhất lớn hơn hoặc bằng Lookup_value. Dữ liệu Lookup_array phải sắp xếp giảm dần.

Nhập các bước sau sau dấu phẩy đã nhập ở bước trước trên dòng Row_num trong hàm INDEX:

1. Nhập 0 (số không) sau dấu phẩy trong hộp văn bản Row_num. Số này khiến hàm lồng trả về khớp chính xác với thuật ngữ nhập trong ô D3 và E3.
2. Nhập ) (dấu ngoặc đơn đóng) để hoàn tất hàm MATCH.

Giữ hộp thoại mở cho bước tiếp theo.

Hoàn tất hàm INDEX

Hàm MATCH đã xong. Bây giờ chuyển đến hộp văn bản Column_num của hộp thoại và nhập đối số cuối cho hàm INDEX. Đối số này nói cho Excel số cột trong phạm vi D6 đến F11. Phạm vi này là nơi nó tìm thông tin được trả về bởi hàm. Trong trường hợp này, là nhà cung cấp cho "widgets titanium".

1. Đặt con trỏ vào hộp văn bản Column_num.
2. Nhập 3 (số ba). Số này bảo công thức tìm dữ liệu ở cột thứ ba của phạm vi D6 đến F11.

Giữ hộp thoại mở cho bước tiếp theo.

Tạo công thức mảng (Array Formula)

Trước khi đóng hộp thoại, chuyển hàm lồng thành công thức mảng. Mảng này cho phép hàm tìm nhiều thuật ngữ trong bảng dữ liệu. Trong hướng dẫn này, hai thuật ngữ được khớp: "Widgets" từ cột 1 và "Titanium" từ cột 2.

Để tạo công thức mảng trong Excel, nhấn đồng thời phím CTRL, SHIFT và ENTER. Khi nhấn, hàm sẽ được bao quanh bởi dấu ngoặc nhọn, cho biết hàm hiện là mảng.

1. Chọn OK để đóng hộp thoại. Trong Excel cho Mac, chọn Done.
2. Chọn ô F3 để xem công thức, sau đó đặt con trỏ ở cuối công thức trong Thanh công thức (Formula Bar).
3. Để chuyển công thức thành mảng, nhấn CTRL+SHIFT+ENTER.
4. Lỗi #N/A xuất hiện trong ô F3. Đây là ô chứa hàm.
5. Lỗi #N/A xuất hiện vì ô D3 và E3 trống. D3 và E3 là ô hàm tìm Lookup_value. Sau khi thêm dữ liệu vào hai ô này, lỗi sẽ được thay bằng thông tin từ cơ sở dữ liệu.

Thêm tiêu chí tìm kiếm

Bước cuối là thêm từ khóa tìm kiếm vào trang tính. Bước này khớp thuật ngữ "Widgets" từ cột 1 và "Titanium" từ cột 2.

Nếu công thức tìm thấy khớp cho cả hai thuật ngữ trong các cột thích hợp trong cơ sở dữ liệu, nó trả về giá trị từ cột thứ ba.

1. Chọn ô D3.
2. Nhập Widgets.
3. Chọn ô E3.
4. Gõ Titanium, và nhấn Enter.
5. Tên nhà cung cấp, Widgets Inc., xuất hiện trong ô F3. Đây là nhà cung cấp duy nhất bán "Titanium Widgets".
6. Chọn ô F3. Hàm xuất hiện trong thanh công thức phía trên trang tính.

{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}

Trong ví dụ này, chỉ có một nhà cung cấp cho "titanium widgets". Nếu có nhiều hơn một, nhà cung cấp liệt kê đầu tiên trong cơ sở dữ liệu sẽ được hàm trả về.