VinaOnline.net

Sử dụng hàm VLOOKUP và HLOOKUP : Hướng dẫn áp dụng chi tiết

Sử dụng hàm VLOOKUP và HLOOKUP là thực hiện tính toán tương đối phức tạp đối với bảng tính Excel. Trong một số trường hợp, hàm IF không thể thực hiện được. Vì vậy, bạn phải sử dụng hàm VLOOKUP và HLOOKUP để tính toán.

I. Sử dụng hàm VLOOKUP và HLOOKUP : Công dụng

Hai hàm này dùng để dò tìm và trả về kết quả nếu dò tìm so khớp.

– Sử dụng VLOOKUP để dò tìm bảng tham chiếu theo dạng cột.

– Hàm HLOOKUP để dò tìm theo dạng hàng.

II. Sử dụng hàm VLOOKUP và HLOOKUP : Cú pháp

Vinaonline.net giới thiệu cú pháp của hàm VLOOKUP, hàm HLOOKUP thực hiện tương tự.

Cú pháp:

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

Chuyển thể tiếng Việt

VLOOKUP(Giá trị, Bảng tham chiếu, Số thứ cột cho kết quả, cách dò tìm)

* Giá trị: Là một ô (xét trong bảng tính). Nếu ô này chưa so khớp với cột đầu tiên trong Bảng tham chiếu, bạn có thể sử dụng các hàm xử lý chuỗi để tách cho phù hợp.

* Bảng tham chiếu: Là một bảng nhỏ (gồm nhiều cột) được đính kèm với bảng tính.

Chú ý:

– Bạn phải cố định Bảng tham chiếu, Nếu không, sao chép (copy) sẽ sai kết quả.

* Số thứ tự cột cho kết quả: Tùy theo yêu cầu, số thứ tự cột có thể 2, 3, 4,…

* Cách dò tìm: Phụ thuộc vào cột đầu tiên của Bảng tham chiếu.

+ 1 hoặc bỏ tham số này: Nếu được sắp thứ tự tăng dần.

+ 0: Ngược lại. TUY NHIÊN, 0 đúng mọi trường hợp.

Hàm HLOOKUP tương tự, chỉ khác Bảng tham chiếu gồm nhiều hàng.

HLOOKUP(Giá trị, Bảng tham chiếu, Số thứ hàng cho kết quả, cách dò tìm)

III. Sử dụng hàm VLOOKUP và HLOOKUP : Ví dụ áp dụng

1. Ví dụ 1

Yêu cầu

1. Điền STT

2. Dựa vào MÃ HÀNG và dò tìm trong BẢNG THAM CHIẾU, điền TÊN HÀNG tương ứng.

3. Cột ĐƠN GIÁ thực hiện tương tự cột MÃ HÀNG

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ

Giải gợi ý

1. Điền STT, bạn tự thực hiện

2. Điền cột TÊN HÀNG

Phân tích:

– Bảng tham chiếu có dạng cột (cột MÃ HÀNG, cột TÊN HÀNG, cột ĐƠN GIÁ) nên bạn sử dụng hàm VLOOKUP.

Giá trị (từ ô B5:B10) dùng để dò tìm đối với cột đầu tiên của BẢNG THAM CHIẾU giống nhau nên giữ nguyên.

– Cột TÊN HÀNG là cột 2 trong BẢNG THAM CHIẾU.

– Cột đầu tiên của BẢNG THAM CHIẾU tăng dần (A, B, C)

Công thức TÊN HÀNG (C5) = VLOOKUP(B5,$B$14:$D$16,2,1)

3. Điền cột ĐƠN GIÁ

ĐƠN GIÁ (E5) = VLOOKUP(B5,$B$14:$D$16,3,1)

4. Cột THÀNH TIỀN, bạn tự thực hiện

2. Ví dụ 2

Yêu cầu

 1. Dựa vào MÃ HÀNG và BẢNG THAM CHIẾU 1, điền TÊN HÀNG.

2. Dựa vào MÃ HÀNG và BẢNG THAM CHIẾU 2, điền LOẠI.

3. SỈ/LẺ: Nếu ký tự cuối cùng là S, ghỉ “Sỉ”. Ngược lại, ghi “Lẻ”.

4. Cột ĐƠN GIÁ thực hiện giống TÊN HÀNG.

5. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ

Giải gợi ý

1. TÊN HÀNG

– BẢNG THAM CHIẾU 1 dạng hàng nên sử dụng hàm HLOOKUP.

– Giá trị (từ B2:B10) không khớp với hàng đầu tiên của BẢNG THAM CHIẾU 1. Dùng hàm cắt chuỗi sao cho được “A”, “B”, “C”. => Sử dụng LEFT(B5,1) trong trường hợp này.

TÊN HÀNG (C5) = HLOOKUP(LEFT(B5,1),$D$13:$F$15,2,1)

4. Cột ĐƠN GIÁ tương tự

ĐƠN GIÁ (G5) = HLOOKUP(LEFT(B5,1),$D$13:$F$15,3,1)

2. LOẠI

– BẢNG THAM CHIẾU 2 dạng cột. Nên sử dụng hàm VLOOKUP.

– Giá trị (từ B5:B10) không khớp với cột đầu tiên của BẢNG THAM CHIẾU 2. Nên dùng hàm MID để cắt lấy ký tự thứ hai. NHƯNG phải sử dụng thêm hàm VALUE vì Giá trị và cột / hàng đầu tiên của BẢNG THAM CHIẾU phải cùng kiểu dữ liệu. => VALUE(MID(B5,2,1))

LOẠI (D5) = VLOOKUP(VALUE(MID(B5,2,1)), $C$19:$D$21,2,1)

3. SỈ/LẺ (E5) = IF(RIGHT(B5,1)=”S”,”Sỉ”,”Lẻ”)

4. THÀNH TIỀN (H5) = F5*G5

IV. Sử dụng hàm VLOOKUP và HLOOKUP : Một số lỗi thường gặp

1. Lỗi #N/A

Khi sao chép toàn bộ các ô:

– Tất cả đều cho lỗi này. Bạn kiểm tra Giá trị và cột đầu tiên của bảng tham chiếu.

– Một vài ô có lỗi #N/A. Bạn kiểm tra dữ liệu cột MÃ HÀNG.

2. Không cố định Bảng tham chiếu

– Ô đầu tiên vẫn đúng, nhưng sao chép sẽ báo lỗi.

Khắc phục bằng cách cố định Bảng tham chiếu (F4)

3. Số thứ tự cột / hàng cho kết quả

Một số đề bài không yêu cầu cụ thể. Trong trường hợp này, bạn sử dụng hàm IF để xét.

Download dữ liệu mẫu

Bạn có thể rèn luyện thêm Bài tập áp dụng VLOOKUP và HLOOKUP.

Kết luận

Sử dụng hàm VLOOKUP và HLOOKUP thành thạo là cả quá trình rèn luyện Excel. Nếu kết hợp tốt với hàm IF, bạn có thể giải mọi bài toán cơ bản. Vinaonline.net chúc bạn sử dụng hàm VLOOKUP và HLOOKUP tốt nhất có thể.