Nếu bạn đã đọc các thủ thuật Excel trong một thời gian, bạn luôn tìm thấy một người nói về việc sử dụng hàm INDEX () & MATCH () của Excel thay vì hàm VLOOKUP trong Excel. Nói cho bản thân tôi, thật khó để cố gắng và thành thạo hai chức năng mới cùng một lúc. Nhưng, đó là một thủ thuật hay. Hãy cho tôi năm phút và tôi sẽ cố gắng giải thích nó bằng tiếng Anh đơn giản.
Bài đánh giá thứ 30 về VLOOKUP
Giả sử bạn có một bảng hồ sơ nhân viên. Cột đầu tiên là số nhân viên và các cột còn lại là nhiều dữ liệu khác nhau về nhân viên. Bất kỳ khi nào bạn có số nhân viên trong trang tính, bạn có thể sử dụng hàm VLOOKUP để trả về dữ liệu cụ thể về nhân viên đó. Cú pháp là VLOOKUP (giá trị, phạm vi dữ liệu, cột số, FALSE). Nó nói với Excel, "Chuyển đến phạm vi dữ liệu. Tìm một hàng có (giá trị) trong cột đầu tiên của phạm vi dữ liệu. Trả lại giá trị (cột số) từ hàng đó. Sau khi bạn hiểu được nó, nó rất đơn giản và mạnh mẽ.
Vấn đề
Một ngày, bạn gặp tình huống có tên nhân viên, nhưng lại cần mã số nhân viên. Trong hình sau, bạn có tên trong A10 và cần tìm mã số nhân viên trong B10.
Khi trường khóa ở bên phải dữ liệu bạn muốn truy xuất, hàm VLOOKUP sẽ không hoạt động. Nếu chỉ hàm VLOOKUP chấp nhận -1 là số cột thì sẽ không có vấn đề gì. Nhưng, nó không. Một giải pháp phổ biến là chèn tạm thời một cột A mới, sao chép cột tên sang cột A mới, điền vào bằng hàm VLOOKUP, Dán Giá trị Đặc biệt, sau đó xóa cột tạm thời A. Các chuyên gia Excel có thể thực hiện động tác này trong chế độ ngủ.
Tôi sẽ đề nghị bạn thực hiện thử thách và thử sử dụng phương pháp bước đơn này. Có, bạn sẽ phải dán công thức lên tường trong vài tuần, nhưng bạn cũng đã làm điều đó với VLOOKUP cách đây khá lâu, phải không?
Tôi nghĩ lý do điều này rất khó khăn là bạn đang sử dụng hai chức năng mà bạn có thể chưa bao giờ sử dụng trước đây. Vì vậy, hãy để tôi chia nó thành hai mảnh.
Đầu tiên, có hàm INDEX (). Đây là một chức năng được đặt tên khủng khiếp. Khi ai đó nói "chỉ mục", nó không gợi lên trong tâm trí tôi bất cứ điều gì tương tự như chức năng này làm. Chỉ mục yêu cầu ba đối số.
=INDEX(data range, row number, column number)
Trong tiếng Anh, Excel đi đến phạm vi dữ liệu và trả về cho bạn giá trị trong giao điểm của (số hàng) hàng và (số cột). Này, hãy nghĩ về nó - điều này khá đơn giản, phải không? =INDEX($A$2:$C$6,4,2)sẽ cung cấp cho bạn giá trị trong B5.
Áp dụng INDEX () cho vấn đề của chúng tôi, bạn có thể tìm thấy để trả lại số nhân viên từ dãy núi này, bạn sẽ sử dụng này: =INDEX($A$2:$A$6,?,1). Trên thực tế, phần này của nó có vẻ tầm thường đến mức nó có vẻ vô dụng. Tuy nhiên, khi bạn thay thế dấu chấm hỏi bằng một hàm MATCH (), bạn có giải pháp.
Đây là cú pháp:
=MATCH(Value, Single-column data range, FALSE)
Nó cho Excel biết, "Tìm kiếm phạm vi dữ liệu và cho tôi biết số hàng tương đối nơi bạn tìm thấy kết quả phù hợp cho (dữ liệu). Vì vậy, để tìm hàng nào có nhân viên trong A10, bạn sẽ sử dụng =MATCH(A10,$B$2:$B$6,FALSE). Vâng, điều này phức tạp hơn Chỉ mục , nhưng nó phải nằm ngay trong ngõ của các chuyên gia VLOOKUP. Nếu A10 chứa "Miller, Bob" thì MATCH này sẽ trả về rằng anh ta đang ở hàng thứ 3 của phạm vi B2: B6.
Đây rồi - hàm MATCH () cho hàm Index biết hàng nào cần tìm - bạn đã hoàn tất. Sử dụng hàm Index, thay thế dấu hỏi của chúng ta bằng hàm MATCH và bây giờ bạn có thể thực hiện tương tự với hàm VLOOKUP khi trường khóa không nằm trong cột bên trái. Đây là chức năng sử dụng:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Ghi chú dán trên tường của tôi thực sự cho thấy nó như hai dòng. Đầu tiên, tôi đã viết giải thích cho MATCH (). Dưới đây tôi đã viết giải thích cho INDEX (). Sau đó, tôi đã vẽ một hình phễu giữa hai hàm để chỉ ra rằng hàm MATCH () giảm xuống đối số thứ 2 của hàm INDEX ().
Vài lần đầu tiên tôi phải làm một trong những điều này, tôi đã bị cám dỗ chỉ để đóng một cột A tạm thời mới vào đó, nhưng thay vào đó tôi đã phải trải qua nỗi đau khi làm theo cách này. Nó nhanh hơn và yêu cầu ít thao tác hơn. Vì vậy, lần tới nếu bạn muốn có thể đặt một số âm trong hàm Vlookup, hãy thử sự kết hợp kỳ lạ giữa INDEX và MATCH này để giải quyết vấn đề của bạn.
