Eddie có một dãy số bộ phận ở định dạng 123/45678 hoặc 011/00345.

Bé cần tra cứu tên của số bộ phận tương ứng từ một bảng khác. Bảng này bao gồm ba cột. Cột đầu tiên chứa phần của số bộ phận trước dấu gạch chéo (chẳng hạn như 123 hoặc 011) và cột thứ hai chứa phần sau dấu gạch chéo (chẳng hạn như 45678 hoặc 00345). Cột thứ ba chứa tên mà Eddie muốn tra cứu.

Một phần của vấn đề mà Eddie đang gặp phải là trong bảng, hai cột mà mỗi cột chứa một phần của số phần là giá trị số. Do đó, phần đầy đủ số không phải là 011 và 00345, mà là 11 và 345.

Eddie đang tự hỏi làm thế nào để đặt cùng một công thức tra cứu cho số bộ phận (123/45678 hoặc 011/00345) để trả về tên bộ phận thích hợp từ bảng.

Để bắt đầu, bạn cần thực hiện một số thay đổi đối với bảng có chứa số bộ phận. Hai cột đầu tiên phải được định dạng dưới dạng văn bản, không phải là số. Điều này sẽ coi các giá trị trong các cột đó là văn bản, để khi bạn nhập “011” vào cột đầu tiên, nó vẫn giữ nguyên số 0 ở đầu.

Nếu bạn đã nhập giá trị là 011 trước khi định dạng cột dưới dạng văn bản, nó sẽ vẫn được hiển thị là 11 (không có số 0 ở đầu). Bạn sẽ không chỉ cần thay đổi định dạng của cột mà còn phải nhập lại bất kỳ đoạn phần-số nào có chứa các số không ở đầu.

Tiếp theo, bạn cần đảm bảo rằng bảng số bộ phận của bạn bao gồm các tiêu đề cột. Đối với ví dụ này, hãy đảm bảo ba tiêu đề cột là Trái, Phải và Tên. (Bạn có thể in đậm và gạch chân các tiêu đề này, điều này giúp đặt chúng khỏi các giá trị trong mỗi cột.) Sau đó, tạo tên cho các cột riêng lẻ bằng cách làm theo các bước sau:

  1. Chọn các ô trong bảng số bộ phận. Đảm bảo rằng bạn cũng chọn các tiêu đề mới được tạo cho mỗi cột của bảng.

  2. Nhấp vào Chèn | Tên | Định nghĩa. Excel sẽ hiển thị hộp thoại Tạo tên.

  3. Đảm bảo rằng chỉ hộp kiểm Hàng trên cùng được chọn.

  4. Bấm OK.

Với bảng số bộ phận của bạn đã chuẩn bị, bây giờ bạn đã sẵn sàng để tra cứu số bộ phận. Trong các ô ngay bên phải giá trị tra cứu của bạn (123/45678 và 011/00345), bạn sẽ nhập một công thức mảng. Giả sử rằng số phần đầu tiên nằm trong ô A1, bạn sẽ nhập công thức mảng sau vào ô B1:

=INDEX(Name,MATCH(A1,Left&"/"&Right,0))

Hãy nhớ rằng để biểu thị đây là công thức mảng, bạn nhập nó bằng cách nhấn Shift + Ctrl + Enter. Sau đó, công thức xuất hiện trong Thanh Công thức với \ {dấu ngoặc nhọn} xung quanh nó. Bạn có thể sao chép công thức xuống các ô khác trong cột B, nếu cần.

Công thức hoạt động bằng cách kéo giá trị từ cột Tên của bảng khi giá trị cột Bên trái tương ứng được nối bằng dấu gạch chéo và giá trị cột Bên phải khớp với bất kỳ giá trị nào trong ô A1. Nếu không khớp, công thức trả về giá trị lỗi # N / A, nếu không, nó trả về số bộ phận mong muốn.

Lưu ý rằng phương pháp này sẽ chỉ hoạt động nếu bạn định dạng hai cột đầu tiên của bảng part-number dưới dạng văn bản và đảm bảo rằng các ô khác nhau chứa bất kỳ số 0 nào ở đầu. Nếu vì lý do nào đó, bạn không thể định dạng hai cột đầu tiên của bảng theo cách này (có lẽ có quá nhiều cột trong số đó), thì bạn cần thay đổi công thức tra cứu đang sử dụng:

=INDEX(Name,MATCH(A1,TEXT(Left,"000")&"/"&TEXT(Right,"00000"),0))

Lưu ý rằng phiên bản này của công thức (vẫn phải được nhập dưới dạng công thức mảng) sử dụng hàm TEXT ở hai vị trí, để chuyển đổi các giá trị từ cột Trái và Cột phải để chúng có các số không ở đầu.

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

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