Video Hàm Vlookup Trong Excel / Top 6 # Xem Nhiều Nhất & Mới Nhất 2/2023 # Top View | Hoisinhvienqnam.edu.vn

Cách Dùng Hàm Vlookup Trong Excel, Có Kèm Video

Video hướng dẫn sử dụng hàm Vlookup

Hàm VLOOKUP trong Excel – Công thức và cách sử dụng cơ bản

Hàm VLOOKUP là gì? Đầu tiên, hàm VLOOKUP là một trong những hàm tìm kiếm trong Microsoft Excel. Hàm VLOOKUP sẽ tìm kiếm giá trị bạn đưa vào và trả kết quả về giá trị cùng dòng ở một cột khác trong bảng.

Hàm VLOOKUP có mặt trong các phiên bản của Excel như Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2007 và các phiên bản cũ hơn.

Công thức của hàm VLOOKUP

Công thức chuẩn của hàm VLOOKUP như sau:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Hàm VLOOKUP có 4 tham số, 3 tham số đầu tiên là bắt buộc phải có, tham số thứ 4 ở trong ngoặc vuông [range_lookup] là không bắt buộc phải có. Trước khi đi tìm hiểu mỗi tham số có ý nghĩa thế nào, bạn hãy xem ví dụ sau đây:

=VLOOKUP(“SP004”,A2:C7,3,FALSE)

lookup_value là giá trị dùng với mục đích tìm kiếm. Giá trị này hoàn toàn có thể thuộc kiểu dữ liệu số, ngày tháng hoặc kiểu chuỗi, kiểu tham chiếu hoặc một giá trị sẽ được tính toán ra bởi một hàm excel khác. Trong công thức trên, chuỗi “SP004” chính là lookup_value.

table_array là vùng dữ liệu gồm ít nhất 2 cột. Hàm VLOOKUP luôn tìm kiếm giá trị dùng để tìm kiếm – lookup_value trong cột đầu tiên của dữ liệu. Trong công thức trên, vùng chính là table_array bao gồm 3 cột, hàm VLOOKUP sẽ tìm kiếm chuỗi “SP004” trong cột đầu tiên của dữ liệu là cột A.

col_index_num chính là thứ tự cột chứa giá trị trả về trong bảng table_array . Trong ví dụ ở trên, table_array là A2:C7, cột C là cột thứ 3 trong bảng tính này.

[range_lookup] là tham số không bắt buộc, sẽ nhận giá trị TRUE hoặc 1 (tìm kiếm gần đúng) hoặc nhận giá trị FALSE hoặc 0 (tìm kiếm chính xác)

tham số của range_lookup khi nhận giá trị FALSE sẽ thực hiện tìm kiếm chính xác cho lookup_value. Trong ví dụ ở trên, VLOOKUP sẽ tìm kiếm chính xác chuỗi “SP004” trong cột A, nếu trong trường hợp không tìm thấy, hàm VLOOKUP sẽ trả về lỗi #N/A

TRUE tham số của range_lookup khi nhận giá trị TRUE sẽ thực hiện tìm kiếm gần đúng, sẽ tìm giá trị lớn nhất trong bảng dữ liệu nhưng giá trị sẽ nhỏ hơn lookup_value. Điều kiện để sử dụng tìm kiếm gần đúng của hàm VLOOKUP là cột tìm kiếm cần được sắp xếp theo thứ tự tăng dần

Trong ví dụ trên, nếu bạn viết chuỗi cần tìm kiếm là “SP004” vào trong công thức VLOOKUP, thì mỗi lần bạn cần tìm kiếm giá trị mới sẽ rất mất công sửa lại công thức, vậy nên, bạn hoàn toàn có thể viết lại công thức như sau:

=VLOOKUP(F1,A2:C7,3,FALSE)

Vậy là mỗi khi bạn cần thay đổi mã sản phẩm, bạn chỉ cần thay đổi dữ liệu trong ô F1, hoặc nếu bạn đã thiết lập mục Data Validation cho ô F1, thì bạn chỉ việc chọn ở danh sách trong Data Validation này.

Hướng dẫn sử dụng hàm VLOOKUP trong thực tế

Trong ví dụ này, bạn cần phải hoàn thiện công việc tra cứu Số lượng và Đơn giá ở cột F và cột G, nếu bạn không khóa tham chiếu tới bảng tra cứu $A$2:$C$7 thì khi bạn kéo công thức từ F2 xuống cho F3 và F4 thì vùng tham chiếu này sẽ bị lệch và dẫn tới kết quả của hàm VLOOKUP không được chính xác.

5 điều bạn cần chú ý khi sử dụng hàm VLOOKUP

Hàm VLOOKUP không thể trả về kết quả khi trường hợp cột lấy kết quả nằm về phía bên trái của cột chứa dữ liệu bạn cần tra cứu. Phương án xử lý: sử dụng kết hợp hàm Index và Match

Hàm VLOOKUP tra cứu không phải phân biệt chữ hoa hay chữ thường.

Chú ý tới tham số cuối cùng [range_lookup] để bạn lựa chọn đúng TRUE hay FALSE trong những trường hợp tra cứu gần đúng hoặc tra cứu chính xác.

Khi tra cứu gần đúng, chú ý dữ liệu trong cột tra cứu cần được sắp xếp theo thứ tự tăng dần

Khi giá trị cần tra cứu không được tìm thấy, kết quả hàm VLOOKUP sẽ trả về lỗi #NA.

Sử dụng hàm VLOOKUP tra cứu ở 1 sheet khác

=VLOOKUP(A2,’Ví dụ 02′!$A$2:$C$7,2,FALSE)

Hướng dẫn sử dụng hàm VLOOKUP để tra cứu ở 1 file Excel khác

=VLOOKUP(A2,'[wb khác.xlsx]Ví dụ 02′!$A$2:$C$7,2,FALSE)

Trong trường hợp wb khác.xlsx đóng, thì công thức VLOOKUP của bạn vẫn sẽ hoạt động, nhưng trong Excel sẽ tự động “viết lại” theo công thức VLOOKUP với đường dẫn tới file dữ liệu như sau:

=VLOOKUP(A2,’C:UsersthanhnguyenDesktop[wb khác.xlsx]Ví dụ 02′!$A$2:$C$7,2,FALSE)

Hướng dẫn sử dụng hàm VLOOKUP và ký tự đại diện *

Giống như nhiều hàm Excel khác, hàm VLOOKUP hỗ trợ bạn sử dụng ký tự thay thế như

Dấu * để thay thế cho bất kì số lượng ký tự nào trong tham số lookup_value

Dấu ? để thay thế cho 1 và chỉ 1 ký tự trong tham số lookup_value

Ví dụ tìm kiếm mã sản phẩm bắt đầu hoặc kết thúc bởi một chuỗi

=VLOOKUP(“*AX”,A2:B7,2,FALSE)

Trong ví dụ này, dấu * thay thế cho cả đoạn “SP001-ABC-” trong dữ liệu của chúng ta.

Nếu bạn muốn tra cứu chuỗi thông tin có chứa chữ “GFC” ở khoảng giữa, thì công thức VLOOKUP bạn có thể viết công thức VLOOKUP như sau:

=VLOOKUP(“*GFC*”,A2:B7,2,FALSE)

Để công thức của bạn linh hoạt hơn, bạn hoàn toàn có thể tách phần “GFC” ra 1 ô riêng – ô G1 chẳng hạn, thì công thức hàm VLOOKUP của bạn sẽ như sau:

=VLOOKUP(“*” & G1 & “*”,A2:B7,2,FALSE)

=VLOOKUP(“ZZ*”,A2:B7,2,FALSE)

Trong ví dụ này, bạn sử dụng “ZZ*” để tra cứu và tìm kiếm ô dữ liệu trong bảng bắt đầu bằng “ZZ”

Tra cứu gần đúng với VLOOKUP trong Excel

Bây giờ sẽ là lúc chúng ta nhìn kỹ hơn vào tham số thứ 4 trong hàm VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

[range_lookup] có thể nhận giá trị TRUE hoặc giá trị FALSE.

FALSE cho trường hợp tra cứu chính xác mà bạn đã có nhiều trường hợp ví dụ trong bài viết này, nếu bạn ngại viết FALSE vì nó hơi dài thì bạn hoàn toàn có thể thay bằng số 0 với tác dụng tương tự.

TRUE cho trường hợp cần tra cứu gần đúng, thay vì gõ TRUE, bạn hoàn toàn có thể gõ số 1.

Ví dụ tra cứu gần đúng với hàm VLOOKUP như sau, bạn làm công việc quản lý và đưa ra KPI cho các nhân viên bán hàng, tới cuối các tháng, bạn sẽ cần phải tính hoa hồng dựa trên từng bậc doanh thu và lương cho các bạn Sales này, rõ ràng việc sử dụng tra cứu chính xác với hàm VLOOKUP sẽ không giải quyết được vấn đề, còn cách giải quyết bằng cách viết rất nhiều hàm IF lồng nhau sẽ giải quyết được vấn đề này nhưng không phải là sự tối ưu nhất. Bạn sẽ thực hiện như sau:

F2=VLOOKUP(E2,$A$2:$B$7,2,TRUE)

Lưu ý khi bạn sử dụng hàm VLOOKUP ở dạng này là: sắp xếp cột dữ liệu (Cột A trong trường hợp này) theo thứ tự sắp xếp từ nhỏ tới lớn. Bởi vì, hàm VLOOKUP sẽ trả về giá trị gần nhất và giá trị nhỏ hơn lookup_value – giá trị cần tìm kiếm. Như vậy kết quả #N/A bạn đang gặp phải trong ví dụ này là hoàn toàn đúng với cách hoạt động của hàm VLOOKUP, bởi vì trong cột A – Doanh thu không hề có giá trị nào nhỏ hơn 96 cả. Để xử lý lỗi #N/A trong trường hợp này, bạn hoàn toàn có thể kết hợp thêm hàm IFNA hoặc IFERROR như sau:

F2=IFNA(VLOOKUP(E2,$A$2:$B$7,2,TRUE),”Không có hoa hồng”)

F2=IFERROR(VLOOKUP(E2,$A$2:$B$7,2,TRUE),”Không có hoa hồng”)

Hàm Vlookup Trong Excel, Cách Dùng Hàm Vlookup Trong Excel, Hàm Tìm Ki

Cách sử dụng hàm Vlookup trong Excel, cú pháp cũng như các ví dụ sẽ được chúng tôi chia sẻ tất tần tật trong bài viết sau đây. Các bạn cùng tham khảo để sử dụng hàm Vlookup cho đúng.

Cách thực hiện này giúp bạn:– Dùng Vlookup hiệu quả– Biết cách sửa lỗi hàm VLOOKUP– Biết cách kết hợp hàm Vlookup với các hàm khác

Hàm VLOOKUP trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật , chúng tôi sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này.

Ví dụ hàm Vlookup, bài tập hàm Vlookup trong Excel

cách sử dụng hàm Vlookup trong Excel 2016, 2013, 2010, 2007

I. Cú pháp hàm Vlookup

Lưu ý cú pháp hàm: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value: Giá trị dùng để dò tìm.

table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4).

col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.

range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).

II. Ví dụ về hàm Vlookup trong Excel 1. Cách dò tìm tương đối:

VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới:

Ví dụ về hàm Vlookup excel

Ta sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)Kết quả thu được:

Kết quả khi dùng hàm vlookup

Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.

Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup

Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)A6 là giá trị đem dò tìm$D$12:$F$17 là bảng dò tìm2 : số thứ tự cột dữ liệu trên bảng dò0 : Kiểu dò tìm chính xác

Cách sử dụng dò tuyệt đối với hàm Vlookup

Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:

Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)

Kết quả dùng hàm Vlookup dò giá trị tuyệt đối

III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match

Ở nội dung trên chúng tôi đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match.

Yêu cầu cần giải quyết:

Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng – Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10).VD: KD-NĐ Tức là gạo Khang Dân – Nam Định.Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10).Câu 3: Tính thành tiền = Số lượng * Đơn giá

Câu 3: Tính thành tiềnỒ, câu hỏi này thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, … Khá là dễ rồi phải không các bạn.Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5

IV. Sửa lỗi #NA hàm Vlookup Lỗi #NA là gì?

Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp

Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiệm tìm kiếm trong vùng điều kiến, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup

Cách sửa lỗi #NA trong hàm Vlookup

Trong Excel, hàm Vlookup được sử dụng phổ biến giúp bạn có thể thực hiện hàm tìm kiếm giá trị, trả về phương thức hàng dọc tốt, nhanh chóng. Hiện nay, bạn có thể áp dụng một trong hai cách dò hàm Vlookup.

Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính.

Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được chúng tôi giới thiệu. Hàm Hllookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra.

Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất.

Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel

https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-trong-excel-633n.aspx Sử dụng Excel không thể không nhắc tới Hàm MID, đây là hàm cơ bản trong Excel hỗ trợ cắt những chuỗi ký tự trong một dãy ký tự cho trước trong bảng tính Excel, hàm cơ bản MID được sử dụng rất nhiều trong các trường hợp cần lấy ra những ký tự đặc biệt nhất trong các chuỗi ký tự dài có thể lên tới hàng trăm ký tự

Hàm Vlookup Là Gì? Cách Sử Dụng Hàm Vlookup Trong Excel

Có nhiều người luôn thắc mắc về hàm Vlookup là gì? Nên các chuyên gia HP Connect sẽ giúp các bạn giải đáp vấn đề sau đây:

V chính là từ viết tắt trong tiếng Anh của Vertical (có nghĩa là theo chiều dọc). Còn Lookup có nghĩa là tìm kiếm. Do đó, Vlookup chính là một hàm cho phép người dùng thực hiện tìm kiếm về một giá trị nào đó thuộc trong một cột dữ liệu. Sau đó, kết quả được trả về là một giá trị phù hợp trong cột hay là giá trị từ cột nào đó với giá trị tương ứng.

Hàm Vlookup có điều kiện này đã được tích hợp trên Excel 2000, Excel 2007, Excel 2010 và Excel 2013. Nên tất cả người dùng đều có thể sử dụng hàm Vlookup có 2 điều kiện này một cách tiện lợi nhất.

Cú pháp của hàm Vlookup Excel là gì?

Hàm Vlookup Excel có cú pháp như sau:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]

Ở cú pháp của hàm Vlookup, các bạn sẽ điền vào tất cả là 4 giá trị. Bao gồm:

Lookup_value: Đây là giá trị bắt buộc

Lookup_value là một giá trị tương đương với một ô nhất định tại bảng Excel. Ví dụ như ô A1, D2, E5,… Là một giá trị ban đầu được sử dụng để tìm kiếm.

Table_array: Đây là giá trị bắt buộc

Cột đầu tiên thường sẽ là cột chứa giá trị tương tự như Lookup_value. Tại cột tiếp theo sẽ có số thứ tự là cột 2 và cứ lần lượt cho các cột sau là cột 3 và cột 4 nếu có.

Thông thường, bảng quy ước đó đã được cố định. Vì vậy, khi đã thực hiện quá trình kéo chọn bảng hoàn tất. Các bạn hãy nhấn tổ hợp phím Fn + F4. Như vậy, giá trị trong bảng sẽ không bị thay đổi.

Col_index_num: Đây là giá trị bắt buộc

Đối với trường hợp các bạn muốn kết quả của giá trị tìm kiếm tại cột 1 thuộc trong bảng Excel sẽ trả về giá trị tại cột 2. Vậy bạn gõ số 2 vào giá trị Col_index_num này. Còn đối với trường hợp bạn muốn được trả về giá trị tại cột 3 trong bảng, bạn chỉ cần nhập vào số 3. Với quy ước về số thứ tự của cột trong bảng đã được đề cập ở trên.

Range_lookup: Đây là giá trị tùy chọn

Range_lookup chính là một phương pháp dò tìm với quy ước như sau:

Quy ước 0: là phương pháp dò tìm tuyệt đối

Quy ước 1: là phương pháp dò tìm tương đối.

Thông thường, hầu hết người dùng sẽ chọn 0 để thực hiện phương pháp dò tìm tuyệt đối.

Hàm Vlookup được người dùng sử dụng trong trường hợp muốn dò tìm thông tin tại một trường dữ liệu bất kỳ. Hoặc là dò tìm danh sách thông qua các mã định danh đã có sẵn.

Ví dụ, các bạn thực hiện nhập hàm Vlookup cùng với mã sản phẩm sang bảng tính khác. Như vậy, thông tin sản phẩm tương ứng của mã sản phẩm đó sẽ được hiển thị. Thường thì các thông tin hiển thị đó sẽ là tên sản phẩm, giá sản phẩm, mô tả, số lượng hàng tồn kho,… Điều đó sẽ tùy thuộc vào nội dung của hàm mà các bạn đã biết.

Ví dụ về hàm Vlookup trong Excel

Excel hàm Vlookup với phương pháp dò tìm tương đối

Dựa vào bảng quy định về xếp loại theo số điểm đã được đưa ra cụ thể. Hãy thực hiện sắp xếp lại loại học lực cho tất cả những học sinh thuộc danh sách sau đây:

Các bạn áp dụng cú pháp hàm Vlookup Excel đối với cột D6 như sau:

=VLOOKUP(C6,$C$16:$D$20,2,1)

Như vậy, kết quả mà các bạn sẽ nhận được là:

Excel hàm Vlookup với phương pháp dò tìm tuyệt đối

Đối với phương pháp dò tìm tuyệt đối trong hàm Vlookup. Các bạn sẽ nhận được kết quả chi tiết hơn so với phương pháp dò tìm tương đối như ở trên.

Ví dụ: Hãy điền vào bảng sau đây các thông tin về Quê quán và Trình độ của tất cả các nhân viên trong bảng. Dựa vào mã nhân viên đã được quy định sẵn ở bảng dưới.

Để thực hiện điền vào thông tin về quê quán của tất cả nhân viên trong danh sách. Các bạn áp dụng cú pháp hàm Vlookup đối với ô E6 cụ thể như sau:

=VLOOKUP(A6,$D$12:$F$17,2,0)

Trong đó:

A6 chính là giá trị được sử dụng để tiến hành dò tìm

$D$12:$F$17 chính là bảng để dò tìm

2 là số thứ tự của cột thuộc trong bảng dữ liệu mà bạn đang dò tìm

0 chính là loại dò tìm chính xác nhất.

Như vậy, việc điền vào ô Trình độ của các nhân viên trong bảng cũng được thực hiện tương tự. Với cú pháp hàm Vlookup ngược cho ô F6 như sau:

Thông qua cách dò tìm với giá trị tuyệt đối, các bạn sẽ được phép tìm kiếm với kết quả hiển thị cụ thể nhất. Và tất nhiên là các giá trị tuyệt đối sẽ được giữ nguyên cho dù hiển thị kết quả tại những ô khác hay cột khác. Vì vậy, đối với những trường hợp cần dò tìm kết quả chính xác, các bạn hãy sử dụng cách dò tìm với giá trị tuyệt đối. Còn giá trị tương đối sẽ được sử dụng trong trường hợp bạn muốn dò tìm với kết quả hiển thị gần chính xác. Có lẽ ví dụ ở trên đã giúp các bạn hiểu hơn về vấn đề này.

Hàm Vlookup kết hợp với hàm Hlookup, Left, Right và Match

Với dữ liệu được cho là bảng bên dưới với quy ước cụ thể:

Bảng 1: Bảng thống kê về tiêu thụ gạo

Bảng 2: Bảng tra tên hàng, tên hãng sản xuất và đơn giá

Một số vấn đề cần được giải quyết như sau:

Vấn đề 1: Dựa vào 2 ký tự phía bên trái cùng với 2 ký tự phía bên phải thuộc trong cột Mã Sp tại bảng 1. Các bạn hãy dò tìm tại bảng 2 để có thể tìm ra giá trị của cột Tên Hàng – Tên hãng sản xuất của bảng 1. Có nghĩa là bạn cần tính dữ liệu chính xác cho cột C5:C10 trong bảng 1.

Ví dụ như Mã SP BH-TB có nghĩa là loại gạo Bắc Hương – Thái Bình

Vấn đề 2: Hãy tiến hành nhập vào đơn giá đối với các mặt hàng căn cứ vào từng Mã SP tại bảng 1 cùng với việc tra tại bảng 2. Có nghĩa là bạn cần tính dữ liệu chính xác cho cột D5:D10.

Vấn đề 3: Hãy tính thành tiền cho mỗi mã sản phẩm tại bảng 1?

Giải quyết từng vấn đề: Vấn đề 1:

Trước hết, các bạn cần phải tìm ra công thức để lấy được dữ liệu về Tên hành cùng với Tên tỉnh sản xuất. Từ đó, các bạn ghép 2 công thức đó lại với nhau để tìm ra được câu trả lời cho vấn đề 1.

+ Lấy Tên Hàng: các bạn sẽ lấy 2 ký tự phía bên trái của mã sản phẩm nằm ở bảng 1 (từ A5:A10). Được so với 2 ký tự thuộc cột mã hàng thuộc bảng thứ 2. Tuy nhiên, vùng dữ liệu mà các bạn lấy qua cột Tên hàng tại Bảng 2 là (A15:B20) hay là (A15:E20).

Các bạn áp dụng hàm Vlookup như sau: Tại một ô bất kỳ trong bảng chưa có dữ liệu, bạn tiến hành nhập vào công thức: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)

Sau khi bạn đã nhập công thức ở trên vào, bạn nhấn Enter để xem kết quả. Nếu như kết quả là “Khang Dân”. Có nghĩa là các bạn đã thực hiện được 40% cho vấn đề 1. Hãy tiếp tục bên dưới nhé!

+ Lấy Tên Tỉnh Sản xuất: Bạn tiến hành lấy 2 ký tự nằm phía bên phải thuộc cột mã SP trong bảng 1 (A5:A10) so với 2 ký tự thuộc trong cột Mã gạo – Tên tỉnh sản xuất tại bảng 2. Vùng dữ liệu mà các bạn sẽ lấy là (A16:E20).

Vì chúng ta sẽ lấy dữ liệu theo hàng nên sẽ sử dụng đến hàm Vlookup Hlookup. Các bạn cũng nhập vào 1 ô trống bất kỳ chưa chứa dữ liệu với công thức như sau:

= HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)

Sau khi bạn dã nhập xong công thức trên vào, bạn nhấn Enter để tiếp tục. Nếu kết quả hiển thị ra là “Nam Định”. Có nghĩa là bạn đã thực hiện thêm 40% câu trả lời cho vấn đề 1. Việc tiếp theo mà các bạn cần làm là hãy ghép 2 công thức của 2 hàm ở trên lại để tiến hành tính dữ liệu cho cột Tên Hàng – Tên Tỉnh Sản xuất.

Ghép 2 công thức: Có rất nhiều công thức để các bạn sử dụng khi bạn muốn ghép hay nối các công thức lại với nhau. Ở ví dụ này, các chuyên gia HP Connect sẽ chia sẻ đến các bạn thực hiện cách nối chuỗi để áp dụng hàm &. Ở vấn đề 1 này, chúng tôi sẽ lấy dấu gạch ngang kèm theo dấu cách ở giữa 2 công thức đã có kết quả ở phía trên. Có nghĩa là kết quả của Tên Hàng và Tên tỉnh sản xuất. Với hàm cụ thể sau đây:

Sau khi ghép 2 hàm lại các bạn sẽ có được hàm mới như sau:

=+vlookup&” – “&hlookup

Chú ý: Tại hàm Hlookup sẽ không có dấu = như ban đầu.

Tại C5, các bạn hãy nhập vào công thức như sau:

C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)

Khi bạn nhấn phím Enter, kết quả sẽ hiển thị ngay. Nếu như kết quả cuối cùng là “Hàng Dân – Nam Định”. Như vậy, các bạn đã nhận được kết quả chuẩn nhất.

Tiếp theo là tại ô C6:C10 trong bảng. Các bạn chỉ cần thực hiện thao tác đưa trỏ chuột vào bên cạnh công thức tại ô C5 rồi bạn kéo xuống đến ô C10. Cũng tương tự như trên, các bạn nhập vào ô C6 với công thức như sau:

C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)

Như vậy là vấn đề 1 đã được bạn giải quyết hoàn thành. Bây giờ hãy tiếp tục đi đến vấn đề 2 nhé!

Vấn đề 2: Tính đơn giá

Các bạn sẽ tính đơn giá theo công thức như sau:

Tại ô D5 trong bảng, các bạn hãy nhập vào công thức:

D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20,MATCH(RIGHT(A5,2),$A$16:$E$16,0),FALSE)

Vấn đề 3: Tính thành tiền

Vấn đề này hay có trong các bài toán về tính các khoản chi phí, tính tiền lương,… Vấn đề này sẽ được giải quyết rất dễ dàng.

Các bạn chỉ cần nhập vào ô F5 công thức như sau khi muốn tính thành tiền:

E5=+D5*E5

Cách sửa Lỗi #NA trong hàm Vlookup như thế nào?

Trước khi đi vào tìm hiểu về cách sửa lỗi, các bạn hãy cùng chúng tôi tìm hiểu xem #NA trong hàm Vlookup là lỗi gì nhé!

#NA chính là một lỗi thường xuất hiện khi công thức bạn nhập vào Excel không tìm ra giá trị phù hợp.

Trong quá trình sử dụng đến hàm Vlookup, các bạn sẽ gặp lỗi #NA khi bạn không tìm thất được điều kiện tìm kiếm thuộc vùng điều kiện. Hay nói theo cách chính xác hơn là tại cột đầu tiên của vùng điều kiện khi sửa dụng hàm Vlookup.

Cách sửa hàm Vlookup bị lỗi #NA

Bước 1: Tiến hành kiểm tra công thức so sánh tại các cột, hàng ở bảng Excel.

Với công thức của hàm VlookUP

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Ý nghĩa của các giá trị đã được giải thích rõ ở trên.

Dựa vào bảng ở trên, các bạn có thể thấy được ô C10 chính là giá trị dò tìm. Còn giá trị tuyệt đối là $F$10:$G$13 và số 2 chính là thứ tự hiển thị tại bảng đang dùng để dò tìm. Cuối cùng, 0 là giá trị dò tìm tuyệt đối.

Từ đó, các bạn có thể thấy lỗi xảy ra không phải là do công thức. Hàm vlookup bị lỗi #N/A là do tại bảng tra không có bất kỳ một giá trị nào có mã là A005.

Bước 2: Các bạn hãy thêm vào giá trị cho mã A005 theo tên của mặt hàng mà các bạn muốn. Thay đổi lại công thức có cột giá trị dò tìm tuyệt đối sẽ được kéo dài hơn. Đồng thời, mã A005 sẽ là kem tại ô G14 trong bảng Excel.

Bước 3: Chọn Enter rồi bạn kéo thả xuống phía dưới để thực hiện việc sao chép công thức một cách hàng loạt. Cuối cùng, kết quả sẽ được hiển thị như sau:

Bên cạnh hàm Vlookup thì trong Excel còn có hàm vlookup if, hàm vlookup và if kết hợp với nhau,… Tất cả sẽ cho các bạn những kết quả chính xác và nhanh chóng nhất.

Hàm If Kết Hợp Với Vlookup Và Hàm And, Or Trong Excel

Trong bài viết này chúng ta cùng tìm hiểu xem làm thế nào để kết hợp IF với Vlookup hay IF với And và OR trong Excel. Trên đã giới thiệu chi tiết về cách sử dụng hàm IF, hàm Vlookup, tuy nhiên lại chưa giới thiệu cụ thể với các bạn về hàm And và hàm OR. Vì vậy, trước tiên chúng ta cùng tìm hiểu cách sử dụng hàm AND và OR.

Hàm AND trong excel

– Công dụng hàm AND: Trả về kết quả là TRUE nếu TẤT CẢ các đối số của hàm có giá trị là TRUE và trả về kết quả là FALSE nếu có ít nhất MỘT đối số của hàm có giá trị là FALSE.

Tức là, Hàm AND chỉ ĐÚNG khi tất cả các đối số (điều kiện) đều ĐÚNG, các trường hợp còn lại là SAI.

cú pháp hàm AND: =AND(logical1, [logical2], …)

– logical1: là đối số bắt buộc, điều kiện thứ nhất phải được trả về giá trị logic.

– logical2, … : tùy chọn, các điều kiện bổ sung tối đa 255 điều kiện.

– Các đối số đều phải trả về kết quả là giá trị logic như TRUE hoặc FALSE.

– Nếu một đối số mảng hoặc tham chiếu chứa văn bản hoặc các ô trống thì sẽ bỏ qua đối số đó.

– Nếu không chứa giá trị logic nào thì hàm AND trả về giá trị lỗi #VALUE!

Ví dụ 2: And(5<6, 10<9) = FALSE.

Trong hàm And() trên có 2 điều kiện: Điều kiện 1 là 5<6, Điều kiện 2 là 10<9. Điều kiện 1 trả về TRUE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm And() trả về sẽ là FALSE.

Hàm OR

– Công dụng hàm OR: Trả về kết quả là TRUE nếu có ít nhất MỘT đối số của hàm có giá trị là TRUE và trả về kết quả là FALSE nếu TẤT CẢ các đối số của hàm có giá trị là FALSE. Tóm lại, Hàm OR chỉ SAI khi tất cả các đối số (điều kiện) đều SAI, các trường hợp còn lại là ĐÚNG.

cú pháp hàm OR: =OR(logical1, [logical2], …)

Trong đó:

– Logical 1: bắt buộc, là một giá trị logic.

– Logical 2: tùy chọn, là một giá trị logic (tối đa 255 điều kiện khác nhau).

Ví dụ 1: Or(6<8, 10<9) = TRUE.

Trong hàm Or() trên có 2 điều kiện: Điều kiện 1 là 6<8, Điều kiện 2 là 10<9. Điều kiện 1 trả về TRUE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm OR() trả về sẽ là TRUE.

Ví dụ 2: Or(8<6, 10<9)= FALSE.

Trong hàm Or() trên có 2 điều kiện: Điều kiện 1 là 8<6, Điều kiện 2 là 10<9. Điều kiện 1 trả về FALSE còn Điều kiện 2 trả về FALSE. Nên kết quả của hàm OR() trả về sẽ là FALSE.

Sau khi đã hiểu cách sử dụng hàm AND và OR, ta tìm hiểu cách sử dụng kết hợp IF với Vlookup và IF với AND, OR.

Hàm IF kết hợp với Vlookup

Cho bảng dữ liệu như sau:

Đầu tiên hàm dò tìm giá trị C4 trong bảng $B$13:$D$17 sau đó kiểm tra giá trị D4 là khu vực nào, nếu là khu vực 1 lấy cột 2 (cột điểm chuẩn 1) là cột trả về, nếu không phải khu vực 1 thì lấy cột 3 (cột điểm chuẩn 2) là cột trả về trong hàm vlookup, sau đó kiểm tra giá trị H4 trong cột tổng điểm có lớn hơn điểm chuẩn không, nếu lớn hơn điểm chuẩn kết quả là Đậu, nếu không lớn hơn kết quả là Rớt.

Trong đó:

“Đậu”: kết quả trả về nếu biểu thức điều kiện trên Đúng

“Rớt”: kết quả trả về nếu biểu thức điều kiện trên Sai

Giải thích công thức VLOOKUP(C4,$B$13:$D$17,IF(D4=”1″,2,3),0)

Trong đó:

C4: là giá trị cần dò tìm (cột ngành thi)

$B$13:$D$17: Bảng dò tìm (có chứa cột ngành thi)

IF(D4=”1″,2,3): Giá trị cột trả về của hàm Vlookup

0: Kiểu dò tìm chính xác

Giải thích công thức: IF(D4=”1″,2,3)

Trong đó:

D4=”1″: Biểu thức điều kiện của hàm IF

2: Giá trị trả về nếu biểu thức điều kiện Đúng

3: Giá trị trả về nếu biểu thức điều kiện Sai

Yêu cầu: Có Học bổng nếu tổng điểm khu vực 1 lớn hơn 22, khu vực 2 lớn hơn 23

Chúng ta kết hợp hàm IF với hàm AND và OR, nhập công thứ như hình dưới và kéo kết quả xuống

Trong đó:

Để làm theo hướng dẫn các bạn có thể tải file mẫu ở liên kết sau: https://goo.gl/5QoLH2