Alan có thể sử dụng xác thực dữ liệu để tạo danh sách thả xuống các lựa chọn hợp lệ cho một ô. Tuy nhiên, những gì anh ta thực sự cần phức tạp hơn. Anh ta có một số lượng lớn các tên mặt hàng với các mã hàng liên quan. Trong ô B2, anh ta có thể tạo một danh sách xác thực dữ liệu hiển thị tất cả các tên mặt hàng (máy khuấy, động cơ, máy bơm, bể chứa, v.v.). Sau đó, người dùng có thể chọn một trong số này. Tuy nhiên, khi anh ta tham chiếu ô B2 ở nơi khác, anh ta muốn mã mặt hàng — không phải tên mặt hàng — được trả về bởi tham chiếu. Do đó, tham chiếu sẽ trả về A, M, P, TK, v.v. thay vì máy khuấy, động cơ, máy bơm, bể chứa, v.v.

Không có cách nào trực tiếp để thực hiện việc này trong Excel. Lý do là vì danh sách xác thực dữ liệu được thiết lập để chỉ bao gồm một danh sách các mục đơn chiều. Điều này giúp danh sách dễ dàng chứa các tên mục của bạn.

Tuy nhiên, bạn có thể mở rộng cách bạn sử dụng danh sách xác thực dữ liệu một chút để có được những gì bạn muốn. Làm theo các bước sau:

  1. Thiết lập một bảng dữ liệu ở vị trí nào đó bên phải dữ liệu trang tính của bạn.

Bảng này sẽ chứa tên mặt hàng của bạn và ở bên phải mỗi tên mặt hàng là mã mặt hàng được liên kết với tên đó.

  1. Chọn các ô chứa tên mặt hàng của bạn. (Không chọn mã hàng, chỉ chọn tên.)

  2. Chọn Chèn | Tên | Định nghĩa. Excel sẽ hiển thị hộp thoại Xác định Tên. (Xem Hình 1.)

  3. Trong hộp Tên, hãy nhập tên mô tả, chẳng hạn như Tên mục. . Bấm OK để thêm tên và đóng hộp thoại.

  4. Chọn ô B2 (ô mà bạn muốn danh sách xác thực của mình).

  5. Chọn Xác thực từ menu Dữ liệu. Excel sẽ hiển thị hộp thoại Xác thực Dữ liệu. (Xem Hình 2.)

  6. Sử dụng danh sách thả xuống Cho phép, chọn Danh sách.

  7. Trong hộp Nguồn, nhập một dấu bằng theo sau tên bạn đã xác định ở bước 4 (chẳng hạn như = ItemNames).

  8. Bấm OK.

Sau khi thực hiện xong các bước này, mọi người vẫn có thể sử dụng danh sách thả xuống xác thực dữ liệu để chọn tên mục hợp lệ. Những gì bạn cần làm bây giờ là tham chiếu mã hàng từ bảng dữ liệu bạn đã thiết lập ở bước 1. Bạn có thể làm điều đó với một công thức như sau:

=VLOOKUP(B2,OFFSET(Itemlist,0,0,,2),2,FALSE)

Công thức này có thể được sử dụng riêng (để đặt mã mục mong muốn vào một ô) hoặc nó có thể được sử dụng trong một công thức lớn hơn, bất kỳ nơi nào bạn đã tham chiếu B2 ban đầu.

Nếu vì lý do nào đó, bạn không thể tạo bảng dữ liệu cho tên và mã mặt hàng của mình, bạn có thể giải quyết vấn đề bằng cách tạo công thức mảng:

=INDEX({"A","M","P","TK"},MATCH(B2,{"agitator","motor","pump","tank"},0))

Như với tất cả các công thức mảng, bạn nhập công thức này bằng cách nhấn Ctrl + Shift + Enter. Hạn chế lớn nhất của nó là nó có thể nhanh chóng trở nên khó sử dụng để giữ cho công thức được cập nhật và có một “giới hạn khả thi” về số lượng cặp mã và mục bạn có thể đưa vào công thức. (Giới hạn được xác định bởi độ dài công thức, vì vậy nó phụ thuộc vào độ dài của tên mặt hàng của bạn.) Ngoài ra, cách tiếp cận này tốt là chỉ trả về mã mặt hàng trong một ô khác, thay vì bao gồm nó như một phần của công thức lớn hơn.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12077) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: