Kimm có một trang tính có ba cột trong đó. Cột A chứa họ của một người, cột B chứa tên và cột C chứa số điện thoại của cá nhân. Nếu Kimm biết họ và tên của người đó (giả sử họ lần lượt nằm trong ô F1 và F2), cô ấy tự hỏi mình sẽ sử dụng loại công thức tra cứu nào để trả về số điện thoại của người đầu tiên khớp với họ và tên đó.

Thực tế có một số công thức khác nhau mà bạn có thể sử dụng để tìm ra số điện thoại. Hầu hết các cách tiếp cận liên quan đến việc sử dụng công thức mảng, công thức này luôn được nhập vào một ô bằng cách sử dụng Ctrl + Shift + Enter. Để làm cho các công thức dễ hiểu hơn, tốt nhất là làm việc với các phạm vi được đặt tên. Ví dụ: thiết lập các tên sau:

  • Chọn tất cả họ, trong cột A, và đặt cho họ một cái tên chẳng hạn như LNames.

  • Chọn tất cả các tên đầu tiên trong cột B và đặt cho chúng một cái tên chẳng hạn như FNames.

  • Chọn tất cả các số điện thoại, trong cột C và đặt tên cho chúng chẳng hạn như Điện thoại.

Đây là nhiều công thức mảng bạn có thể sử dụng để tìm số điện thoại:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

Ngoài ra, bạn có thể xây dựng một công thức mảng dựa trên hàm ROW, như được hiển thị ở đây:

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

Nếu sử dụng phương pháp này (dựa vào hàm ROW), bạn có thể cần điều chỉnh phần “-1” của công thức để phản ánh số hàng xuất hiện trước dữ liệu thực tế của bạn. Trong trường hợp này, hàng bị giảm đi một vì hàng đầu tiên của bảng dữ liệu bao gồm các tiêu đề cho mỗi cột; bản thân dữ liệu bắt đầu ở hàng 2.

Lưu ý rằng tất cả các công thức được sử dụng cho đến nay đều dựa vào việc kết hợp tên đầu tiên với họ để so sánh. Điều này có nguy cơ dẫn đến “dương tính giả” trong một số trường hợp. Ví dụ: giả sử rằng hai trong số những cái tên bạn có trong dữ liệu của mình là Thom Astonfield và Thomas Tonfield. Vì trường hợp của các chữ cái trong tên không quan trọng trong các công thức này, nên khi bạn kết hợp họ và tên của những cá nhân này, chúng hoàn toàn giống nhau. Vì vậy, nếu bạn đang tìm số điện thoại của Thomas Tonfield và tên của anh ấy xuất hiện trong danh sách sau Thom Astonfield, thì bạn sẽ luôn nhận được số điện thoại của Thom thay vì Thomas ‘.

Để giải quyết vấn đề tiềm ẩn này, bạn có thể muốn bao gồm một số loại dấu phân cách giữa họ và tên. Sử dụng một trong các công thức trước đó trong mẹo, tất cả những gì bạn cần làm là điều chỉnh, như được hiển thị ở đây:

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

Việc thêm dấu hai chấm giữa họ và tên hoạt động như một dấu phân cách, loại bỏ khả năng kết quả dương tính giả.

Bạn cũng nên nhận ra rằng nếu các ô F1 và F2 chứa tên hoàn toàn không xuất hiện trong dữ liệu, thì thông tin bạn lấy lại từ công thức sẽ bị sai. Tùy thuộc vào biến thể của hàm được sử dụng, bạn có thể gặp phải tình trạng lỗi thực tế (chẳng hạn như # N / A hoặc #REF)

hoặc bạn có thể nhận lại dữ liệu sai rõ ràng. Trong những trường hợp mà điều kiện lỗi được trả về, bạn có thể muốn điều chỉnh công thức của mình để tính đến khả năng không tìm thấy kết quả phù hợp, theo cách sau:

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

Có một số điều bạn cần lưu ý khi sử dụng các loại công thức này. Đầu tiên và quan trọng nhất, độ tin cậy của thông tin bạn nhận lại sẽ phụ thuộc phần lớn vào chất lượng thông tin trong danh sách dữ liệu của bạn. Nếu dữ liệu của bạn có lỗi chính tả trong đó, chứa khoảng trống, được sắp xếp theo một số thứ tự lạ hoặc có nhiều mục nhập cho cùng một người, điều đó có thể ảnh hưởng đến kết quả công thức trả về.

Nếu bạn không quá chắc chắn về chất lượng dữ liệu của mình, bạn có thể chỉ muốn sử dụng khả năng lọc của Excel hơn là một công thức. Áp dụng Bộ lọc tự động và bạn có thể sử dụng hai cột đầu tiên của dữ liệu để chọn họ và tên. Thao tác này sau đó sẽ trả về bất kỳ số điện thoại nào cho cá nhân bạn chọn. Nó rất đơn giản để làm và làm cho việc lựa chọn dữ liệu bạn cần dễ dàng.

Cuối cùng, bạn nên nhận ra rằng có những cách tiếp cận khác mà bạn có thể sử dụng để giải quyết vấn đề. Ví dụ: bạn có thể tạo một công thức sử dụng hàm DGET, nhưng làm như vậy sẽ yêu cầu thêm một bảng tiêu chí nhỏ vào trang tính hoặc sổ làm việc của bạn. Vì Kimm đã chỉ định rằng cô ấy không thể thêm kết quả trung gian trong một trang tính, nên ban biên tập đã đưa ra quyết định không bao gồm hàm DGET làm giải pháp vì nó sẽ yêu cầu bổ sung bảng tiêu chí.

Ngoài ra, nếu bạn cảm thấy thoải mái với việc sử dụng macro, bạn cũng có thể tạo một hàm do người dùng xác định để kiểm tra dữ liệu và trả về số điện thoại được yêu cầu. Lợi ích của việc sử dụng cách tiếp cận như vậy là nó cung cấp cho bạn sự linh hoạt hơn trong việc xử lý thông tin mà hàm thực sự trả về.

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

Mẹo này (10478) á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: