Giá trị tra cứu với nhiều tiêu chí
Thật dễ dàng để tra cứu giá trị bằng một khóa duy nhất trong bảng. Chúng ta có thể đơn giản sử dụng liên kết: / công thức-và-chức-năng-giới-thiệu-của-vlookup-chức-năng [hàm Vlookup]. Nhưng khi bạn không có cột duy nhất đó trong dữ liệu của mình và cần phải tra cứu trong nhiều cột để khớp với một giá trị, thì hàm VLOOKUP không giúp được gì.
công thức.
Công thức chung để tra cứu nhiều tiêu chí
=INDEX(lookup_range,MATCH(1,INDEX((criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0,1),0))
lookup_range: Là phạm vi mà bạn muốn truy xuất giá trị.
Criteria1, Criteria2, Criteria N: Đây là những tiêu chí bạn muốn đối sánh trong dải ô1, dải ô2 và dải ô N. Bạn có thể có tối đa 270 tiêu chí – cặp phạm vi.
Phạm vi1, phạm vi2, phạm viN: Đây là các phạm vi mà bạn sẽ phù hợp với tiêu chí tương ứng của mình.
Nó sẽ hoạt động như thế nào? Hãy xem… ===== INDEX và MATCH với nhiều tiêu chí Ví dụ Ở đây tôi có một bảng dữ liệu. Tôi muốn lấy tên của khách hàng bằng Ngày đặt phòng, Nhà xây dựng và Khu vực. Vì vậy, ở đây tôi có ba tiêu chí và một phạm vi tra cứu.
Viết công thức này vào ô I4 nhấn enter.
=INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)(I2=B2:B16)(I3=C2:C16),0,1),0))
Cách thức hoạt động:
(I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16): Phần chính là cái này. Mỗi phần của câu lệnh này trả về một mảng true false.
Khi các giá trị boolean được nhân lên, chúng sẽ trả về mảng 0 và 1.
Phép nhân hoạt động như toán tử AND. Rất tiếc khi tất cả giá trị chỉ đúng thì nó trả về 1 khác 0 (I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16) Điều này hoàn toàn sẽ trả về
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}*
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Nó sẽ dịch thành
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEX ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): Hàm INDEX sẽ trả về cùng một mảng (\ {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) thành hàm MATCH dưới dạng mảng tra cứu.
MATCH (1, INDEX ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): Hàm MATCH sẽ tìm kiếm 1 trong mảng \ {0; 0; 0; 0 ; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Và sẽ trả về số chỉ mục của 1 đầu tiên được tìm thấy trong mảng. Ở đây là 8.
INDEX (E2: E16, MATCH (1, INDEX I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1), 0:
Cuối cùng, INDEX sẽ trả về giá trị từ dải ô đã cho (E2: E16) tại chỉ số tìm thấy (8).
Đơn giản ????. Xin lỗi, không thể làm cho nó đơn giản hơn.
Giải pháp mảng Nếu bạn có thể nhấn CTRL + SHIFT + ENTER sau đó bạn có thể loại bỏ hàm INDEX bên trong. Chỉ cần viết công thức này và nhấn CTRL + SHIFT ENTER.
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)(I2=B2:B16)(I3=C2:C16),0))
Công thức Mảng Chung cho Tra cứu nhiều tiêu chí
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0))
Công thức hoạt động giống như giải thích ở trên.
Tôi đã cố gắng hết sức để giải thích nó đơn giản nhất có thể. Nhưng nếu tôi không đủ rõ ràng, hãy cho tôi biết trong phần bình luận bên dưới. Nhân tiện, bạn không cần biết động cơ hoạt động như thế nào để d rive một chiếc xe hơi. Bạn chỉ cần biết cách lái nó. Và bạn biết nó rất rõ.