VLOOKUP 2 file Excel: ghép dữ liệu theo cột khóa, không cần gõ hàm
Công cụ VLOOKUP 2 file Excel này làm đúng việc mà hàm VLOOKUP hay XLOOKUP làm: với mỗi dòng của bảng chính A, tìm dòng có cùng mã trong bảng tra B rồi chép các cột bạn cần sang. Khác ở chỗ bạn chỉ bấm chọn cột, không phải nhớ cú pháp, không lo sai số thứ tự cột hay quên tham số FALSE. Kết quả là chính file A có thêm cột, kèm danh sách dòng không tìm thấy, mã bị trùng ở B và công thức tương đương nếu bạn muốn tự làm lại trong Excel.
Tính năng nổi bật
- Mở bảng chính A và bảng tra B từ file .xlsx, .xlsm, .xls, .ods hoặc .csv (tối đa 50 MB mỗi file), chọn sheet và dòng tiêu đề, nhập 0 khi bảng không có tiêu đề
- Chọn cột khóa ở mỗi bên, ghép tối đa 3 cột thành một khóa (ví dụ mã chi nhánh cộng mã hàng); khi chọn cột ở A, công cụ tự chọn cột cùng tên ở B
- Chọn một hoặc nhiều cột cần lấy từ B bằng cách bấm vào tên cột; cột trùng tên với cột đã có ở A được thêm hậu tố (B)
- Bốn tùy chọn chuẩn hóa khóa: bỏ khoảng trắng thừa (cả khoảng trắng không ngắt dòng), không phân biệt hoa thường, bỏ dấu tiếng Việt, coi số dạng chữ 001, 1 và 1.0 là một
- Xử lý mã trùng ở B theo ba cách: lấy dòng đầu tiên như VLOOKUP, lấy dòng cuối cùng, hoặc nối tất cả giá trị vào một ô; liệt kê từng mã trùng kèm số dòng trong B
- Đếm số dòng tìm thấy, không tìm thấy và khóa trống ở A; lọc xem riêng các dòng không tìm thấy; tự đặt chữ ghi vào ô không tìm thấy (mặc định #N/A, để trống được)
- Tải về chính file A dạng .xlsx: giữ các sheet khác, giá trị, định dạng số, ngày và độ rộng cột cũ; ô chép từ B giữ kiểu số và định dạng số của ô gốc khi khớp đúng một dòng
- Tùy chọn thêm sheet Bao cao tra cuu liệt kê dòng không tìm thấy, khóa trống và mọi mã trùng ở B kèm số dòng
- Nút Sao chép cột kết quả để dán thẳng vào cột trống cạnh bảng A trong Excel đang mở
- Sinh công thức XLOOKUP và VLOOKUP (hoặc INDEX/MATCH khi cột cần lấy nằm bên trái cột khóa, khi ghép khóa) theo đúng tên file, tên sheet, cột và dòng của bạn, có nút sao chép
Khi nào nên dùng công cụ thay vì gõ hàm VLOOKUP
VLOOKUP là hàm được tìm cách dùng nhiều nhất trong Excel, nhưng cũng là hàm hay cho kết quả sai mà người dùng không biết. Lỗi thường gặp: quên tham số FALSE nên Excel dò gần đúng và trả về giá trị của dòng khác; đếm sai số thứ tự cột; mã ở hai file trông giống nhau nhưng một bên có dấu cách thừa ở cuối hoặc là số lưu dạng chữ, nên ra #N/A hàng loạt; bảng tra có mã trùng mà không ai biết nên luôn lấy dòng đầu. Khi hai bảng nằm ở hai file, công thức còn phải tham chiếu sang file khác, dễ hỏng khi đổi tên hoặc di chuyển file. Công cụ này làm cùng phép tra cứu nhưng cho bạn thấy ngay bao nhiêu dòng không khớp, mã nào trùng, và tự chuẩn hóa khóa nếu cần. Kết quả là giá trị cố định trong file, không phụ thuộc file B còn hay mất. Nếu bạn vẫn muốn công thức sống, phần cuối trang cho sẵn XLOOKUP và VLOOKUP viết đúng cho dữ liệu của bạn.
Lợi ích khi sử dụng
- Không phải nhớ cú pháp, số thứ tự cột hay tham số dò tìm chính xác
- Thấy ngay số dòng không khớp thay vì phải lọc #N/A sau khi kéo công thức
- Phát hiện mã trùng trong bảng tra, nguyên nhân hay gặp của số liệu sai
- Khớp được mã có khoảng trắng thừa, khác hoa thường, số lưu dạng chữ
- File kết quả là giá trị cố định, gửi cho người khác không bị lỗi liên kết
- Hai file được đọc trên máy của bạn, không tải lên máy chủ
Cách VLOOKUP 2 file Excel bằng công cụ
- 1Ở khung Bảng chính A, bấm Chọn file và mở file cần điền thêm cột. Chọn Sheet và Dòng tiêu đề nếu khác mặc định.
- 2Ở khung Bảng tra B, mở file chứa dữ liệu cần lấy, chọn Sheet và Dòng tiêu đề tương tự.
- 3Trong phần Cột khóa và cột cần lấy, chọn Cột khóa 1 ở A và ở B. Cần ghép nhiều cột làm khóa thì bấm Thêm cột khóa (ghép khóa).
- 4Bấm vào tên các cột trong mục Cột cần lấy từ B. Chọn các tùy chọn chuẩn hóa, cách xử lý khi khóa bị trùng ở B và chữ ghi vào ô không tìm thấy.
- 5Xem Kết quả ghép: số dòng tìm thấy, không tìm thấy, khóa trùng; bấm Không tìm thấy, khóa trống để kiểm tra các dòng lỗi.
- 6Bấm Tải Excel (.xlsx) để lấy file A đã có thêm cột, hoặc Sao chép cột kết quả để dán vào Excel đang mở.
- 7Muốn tự làm trong Excel, sao chép công thức trong khung Công thức tương đương để tự làm trong Excel.
VLOOKUP hoạt động như thế nào
Cú pháp của hàm là VLOOKUP(giá trị cần tìm, vùng bảng, số thứ tự cột, kiểu dò). Excel tìm giá trị cần tìm ở cột đầu tiên của vùng bảng, gặp dòng khớp thì trả về ô ở cột có số thứ tự đã cho trong cùng dòng. Tham số cuối FALSE (hoặc 0) yêu cầu khớp chính xác; nếu bỏ trống, Excel hiểu là TRUE và dò gần đúng, chỉ đúng khi cột đầu đã sắp xếp tăng dần, và thường trả về giá trị sai mà không báo lỗi. Hàm có ba giới hạn cố hữu: chỉ tìm ở cột đầu tiên của vùng nên không lấy được cột nằm bên trái, chỉ trả về dòng khớp đầu tiên, và số thứ tự cột không tự đổi khi bạn chèn thêm cột vào bảng tra. Công cụ này làm phép dò khớp chính xác như VLOOKUP với FALSE, nhưng không vướng ba giới hạn đó.
XLOOKUP, INDEX/MATCH và công thức được sinh sẵn
XLOOKUP có từ Excel 2021 và Microsoft 365, nhận riêng cột cần tìm và cột cần trả về, nên lấy được cột ở bất kỳ phía nào, có sẵn tham số cho trường hợp không tìm thấy và tham số tìm từ dưới lên để lấy dòng cuối. INDEX kết hợp MATCH là cách làm tương đương cho các bản Excel cũ hơn. Công cụ sinh cả hai loại cho cột cần lấy đầu tiên, dùng đúng tên file, tên sheet, chữ cái cột và dòng bắt đầu của bạn, vùng tra cố định bằng dấu $ để kéo xuống không bị lệch. Khi ghép nhiều cột khóa, công thức nối các cột bằng ký tự | ở cả hai phía; với Excel 2019 trở về trước, công thức mảng này cần nhấn Ctrl+Shift+Enter. Khi chọn nối tất cả giá trị, công thức dùng TEXTJOIN và FILTER, chỉ có ở Excel 2021 và Microsoft 365.
Vì sao VLOOKUP ra #N/A dù nhìn thấy mã giống nhau
Nguyên nhân phổ biến nhất là khoảng trắng: mã được dán từ phần mềm khác có dấu cách ở cuối, hoặc có khoảng trắng không ngắt dòng (mã 160) mà hàm TRIM của Excel không xóa được. Nguyên nhân thứ hai là kiểu dữ liệu: một bên lưu 1001 dạng số, bên kia lưu "1001" dạng chữ, hoặc một bên có số 0 đầu như 001001. Nguyên nhân thứ ba là tiếng Việt: cùng chữ "Hòa" nhưng gõ bằng hai bộ gõ khác nhau có thể ra hai chuỗi Unicode khác nhau (dựng sẵn và tổ hợp), hoặc một bên có dấu, một bên không. Công cụ đưa mọi khóa về Unicode dựng sẵn trước khi so, và các tùy chọn chuẩn hóa xử lý ba nhóm nguyên nhân còn lại. Lưu ý tùy chọn số dạng chữ chỉ hiểu dấu chấm thập phân, không hiểu dấu chấm phân cách hàng nghìn như 1.234.
Ghép nhiều cột làm khóa
Có những bảng mà một cột không đủ xác định một dòng: cùng mã hàng nhưng ở nhiều kho, cùng mã nhân viên nhưng nhiều tháng lương, cùng số đơn nhưng nhiều chi nhánh. Trong Excel, người ta thường thêm cột phụ nối các cột lại bằng dấu & rồi mới VLOOKUP. Công cụ cho chọn tối đa ba cặp cột khóa; một dòng của A chỉ khớp với dòng của B khi mọi cặp đều bằng nhau sau chuẩn hóa. Thứ tự cặp khóa không ảnh hưởng kết quả, nhưng mỗi cặp phải là hai cột cùng nghĩa ở hai bảng. Nếu các cột khóa của một dòng đều trống, dòng đó được đánh dấu khóa trống và không đem đi tìm.
Mã trùng trong bảng tra và cách chọn dòng
Bảng tra lý tưởng có mỗi mã một dòng, nhưng thực tế thường có mã lặp lại do nhập trùng, do bảng giá có nhiều lần cập nhật, hoặc do bảng chi tiết nhiều dòng cho một mã. VLOOKUP luôn trả về dòng đầu tiên và không báo gì, nên người dùng khó biết số liệu đang lấy từ dòng nào. Công cụ liệt kê mọi mã trùng kèm số dòng trong B để bạn kiểm tra, và cho chọn lấy dòng đầu tiên, lấy dòng cuối cùng (hợp với bảng giá cập nhật nối dần xuống dưới) hoặc nối tất cả giá trị vào một ô cách nhau dấu chấm phẩy (hợp khi cần xem đủ các lần xuất hiện). Khi nối tất cả, ô kết quả là chữ, không còn là số để cộng tổng.
File kết quả giữ những gì của file A
Công cụ đọc lại file A gốc, giữ mọi sheet, mọi ô, định dạng số và ngày, độ rộng cột, rồi thêm cột mới ngay bên phải cột cuối cùng đang có dữ liệu của sheet đã chọn. Tiêu đề cột mới đặt ở dòng tiêu đề bạn đã chọn, dữ liệu bắt đầu ở dòng ngay dưới. Khi một dòng A khớp đúng một dòng B, ô mới được chép nguyên kiểu từ ô của B, nên số vẫn là số, ngày vẫn là ngày với định dạng gốc. Thư viện đọc ghi Excel trên trình duyệt không giữ được màu nền, phông chữ, viền ô, định dạng có điều kiện, biểu đồ và macro; công thức có sẵn trong file A vẫn được ghi lại nhưng Excel sẽ tính lại khi mở. Nếu A là .xls, .ods hoặc .csv, file tải về là .xlsx.
Ví dụ thường gặp ở văn phòng
Kế toán có danh sách đơn hàng xuất từ phần mềm bán hàng (A) và bảng giá vốn theo mã hàng (B), cần thêm cột giá vốn vào từng đơn để tính lãi gộp. Nhân sự có bảng chấm công theo mã nhân viên (A) và danh sách nhân viên có phòng ban, chức danh (B). Bộ phận bán hàng có danh sách khách đặt hàng (A) và danh sách khách đã thanh toán (B), cần biết ai chưa trả tiền: chỉ cần lấy cột ngày thanh toán, dòng không tìm thấy chính là khách chưa trả. Giáo viên có danh sách điểm thi theo số báo danh và danh sách lớp có họ tên. Với cả bốn trường hợp, sheet báo cáo cho biết ngay mã nào không có trong bảng tra để xử lý trước khi gửi số liệu đi.
Câu hỏi thường gặp (FAQ)
Làm sao VLOOKUP giữa 2 file Excel khác nhau?
Trong Excel, bạn mở cả hai file rồi viết công thức có tham chiếu dạng '[file B.xlsx]Sheet1'!$A$2:$D$100. Với công cụ này, bạn chỉ cần mở file A và file B, chọn cột khóa và cột cần lấy, rồi tải file A đã có thêm cột. Công cụ cũng sinh sẵn công thức đúng tham chiếu để bạn dán vào Excel nếu muốn.
VLOOKUP 2 file Excel bằng công cụ có cho kết quả giống hệt hàm VLOOKUP không?
Khi tắt các tùy chọn chuẩn hóa và chọn lấy dòng đầu tiên, phép dò giống VLOOKUP với tham số FALSE, trừ một điểm: hàm của Excel không phân biệt hoa thường, nên hãy để bật tùy chọn này nếu cần giống hệt. Khi bật bỏ khoảng trắng, bỏ dấu hay số dạng chữ, công cụ khớp được nhiều dòng hơn công thức.
Vì sao có dòng không tìm thấy dù tôi thấy mã có trong B?
Thường do khoảng trắng thừa, khác kiểu số và chữ, hoặc khác dấu. Hãy bật các tùy chọn chuẩn hóa tương ứng. Nếu vẫn không khớp, kiểm tra xem đã chọn đúng cột khóa và đúng dòng tiêu đề ở hai bên chưa, và mã có chứa ký tự lạ như gạch nối dài hay chữ O thay số 0 không.
Ghép theo 2 điều kiện (2 cột khóa) được không?
Được, bấm Thêm cột khóa (ghép khóa) để thêm cặp cột thứ hai, tối đa ba cặp. Một dòng của A chỉ khớp khi mọi cặp cột đều bằng nhau. Công thức tương đương được sinh bằng cách nối các cột bằng ký tự | ở cả hai bên.
Lấy được cột nằm bên trái cột khóa không?
Được. Công cụ không bị giới hạn như VLOOKUP, bạn chọn bất kỳ cột nào của B. Trong phần công thức, khi cột cần lấy nằm bên trái cột khóa, công cụ đưa ra INDEX/MATCH thay cho VLOOKUP, còn XLOOKUP thì dùng được cho mọi trường hợp.
Mã bị trùng trong file B thì lấy dòng nào?
Bạn tự chọn: dòng đầu tiên (giống VLOOKUP), dòng cuối cùng, hoặc nối tất cả giá trị vào một ô cách nhau dấu chấm phẩy. Danh sách mã trùng hiện ngay dưới kết quả kèm số dòng trong B, và có trong sheet báo cáo để kiểm tra lại.
File tải về có giữ định dạng của file A không?
Giữ giá trị, các sheet, định dạng số, ngày và độ rộng cột. Không giữ được màu nền, phông chữ, viền ô, định dạng có điều kiện, biểu đồ và macro, vì thư viện xử lý Excel trên trình duyệt không ghi được các phần này. Nếu cần giữ nguyên trình bày, hãy dùng Sao chép cột kết quả rồi dán vào file gốc.
Dữ liệu của tôi có bị gửi lên mạng không?
Không. Cả hai file được đọc và xử lý bằng JavaScript ngay trên trình duyệt, file kết quả cũng được tạo tại chỗ. Bạn có thể kiểm tra trong tab Network của công cụ dành cho nhà phát triển: không có yêu cầu nào gửi nội dung file đi.
Hai bảng nằm ở hai sheet của cùng một file thì làm sao?
Mở cùng file đó ở cả khung A và khung B, rồi chọn sheet khác nhau cho mỗi bên. File tải về là file đó với sheet A đã có thêm cột. Trong công thức sinh sẵn, hãy xóa phần [tên file] vì hai bảng đã nằm cùng file.
Có hỗ trợ file CSV không?
Có, cả A và B đều nhận .csv, .tsv hoặc .txt dạng bảng. Công cụ tự đoán dấu phân cách và mã hóa (UTF-8 hoặc Windows-1258). Khi A là CSV, file kết quả được lưu thành .xlsx; ô chỉ gồm chữ số được ghi thành số, còn mã có số 0 đầu giữ dạng chữ.
Số 001 và số 1 có được coi là một không?
Có khi bật tùy chọn Số dạng chữ. Khi đó 001, 1, 1.0 và +1 khớp với nhau. Tùy chọn này chỉ hiểu dấu chấm là dấu thập phân; số có dấu chấm phân cách hàng nghìn như 1.234 sẽ được hiểu là một nghìn hai trăm ba mươi tư phần nghìn, nên với các mã kiểu đó hãy tắt tùy chọn.
Không tìm thấy thì ô kết quả ghi gì?
Mặc định ghi #N/A dạng chữ để dễ lọc. Bạn có thể đổi thành chữ khác như Không có, hoặc xóa trống để ô để rỗng. Dòng có khóa trống ở A luôn để rỗng và được đếm riêng.
File lớn bao nhiêu thì dùng được?
Mỗi file tối đa 50 MB. Phép tra cứu dùng bảng băm nên vài trăm nghìn dòng vẫn chạy nhanh; giới hạn thực tế là bộ nhớ của trình duyệt khi đọc file Excel lớn. Trên điện thoại nên dùng file nhỏ hơn.
VLOOKUP và XLOOKUP khác nhau thế nào?
XLOOKUP nhận riêng cột tìm và cột trả về nên lấy được cột bên trái, mặc định khớp chính xác, có sẵn tham số khi không tìm thấy và cho tìm từ dưới lên. VLOOKUP chỉ tìm ở cột đầu của vùng, cần số thứ tự cột và tham số FALSE. XLOOKUP chỉ có từ Excel 2021 và Microsoft 365, mở bằng bản cũ sẽ báo lỗi #NAME?.
Có dùng trên Google Sheets được không?
Bạn có thể tải file Google Sheets về dạng .xlsx hoặc .csv rồi mở bằng công cụ. Công thức XLOOKUP và VLOOKUP sinh sẵn có cú pháp dùng được trong Google Sheets, nhưng phần tham chiếu sang file khác phải thay bằng IMPORTRANGE vì Google Sheets không hiểu dạng [tên file].
Không VLOOKUP được 2 file Excel thì do đâu?
Hay gặp nhất là mã ở hai file khác kiểu (một bên là số, một bên là chữ), có khoảng trắng thừa hoặc khác chữ hoa thường, khiến hàm trả về #N/A. Lỗi #REF! thường do tham chiếu sang file B bị đổi tên, chuyển thư mục hoặc số thứ tự cột vượt quá vùng bảng. Công cụ này tránh được các lỗi đó vì bạn chọn cột bằng danh sách, có tùy chọn bỏ khoảng trắng, không phân biệt hoa thường, coi 001 và 1 là một, và liệt kê từng dòng không tìm thấy để bạn kiểm tra.
Vì sao công thức liên kết sang file B hay bị lỗi?
Công thức tham chiếu sang file khác lưu đường dẫn tới file B, nên sẽ hỏng khi file B bị đổi tên, chuyển thư mục hoặc gửi file A cho người không có file B. Một số hàm và phép tính mảng còn cần file B đang mở mới tính lại được. Đây là lý do nhiều người chọn tải kết quả dạng giá trị cố định từ công cụ thay vì giữ công thức liên kết.
Từ khóa liên quan
- không vlookup được 2 file excel
- vlookup 2 file excel
- vlookup giữa 2 file excel
- vlookup giữa 2 sheet
- cách dùng hàm vlookup giữa 2 file
- ghép dữ liệu 2 file excel
- ghép cột từ file khác excel
- vlookup không cần công thức
- vlookup online
- xlookup 2 file excel
- vlookup nhiều điều kiện
- vlookup 2 điều kiện
- vlookup bị n/a
- lỗi #n/a vlookup
- index match giữa 2 file
- dò tìm dữ liệu giữa 2 file excel
- lấy dữ liệu từ file excel khác
- vlookup lấy cột bên trái
- merge 2 excel files by column
- vlookup between two workbooks
- tra cứu mã hàng excel
- vlookup có dấu tiếng việt
- nối bảng excel theo mã