{"id":11889,"date":"2025-01-15T05:53:15","date_gmt":"2025-01-15T05:53:15","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/loi-value-cua-ham-vlookup-nguyen-nhan-va-cach-khac-phuc\/"},"modified":"2025-01-15T05:53:15","modified_gmt":"2025-01-15T05:53:15","slug":"loi-value-cua-ham-vlookup-nguyen-nhan-va-cach-khac-phuc","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/loi-value-cua-ham-vlookup-nguyen-nhan-va-cach-khac-phuc\/","title":{"rendered":"L\u1ed7i #VALUE c\u1ee7a h\u00e0m VLOOKUP, nguy\u00ean nh\u00e2n v\u00e0 c\u00e1ch kh\u1eafc ph\u1ee5c"},"content":{"rendered":"
\n

N\u00f3i chung, Microsoft Excel s\u1ebd hi\u1ec3n th\u1ecb l\u1ed7i #VALUE! n\u1ebfu gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong c\u00f4ng th\u1ee9c c\u00f3 ki\u1ec3u d\u1eef li\u1ec7u sai. \u0110\u1ed1i v\u1edbi h\u00e0m VLOOKUP<\/a>, c\u00f3 hai tr\u01b0\u1eddng h\u1ee3p th\u01b0\u1eddng g\u1eb7p g\u00e2y l\u1ed7i VALUE!. H\u00e3y c\u00f9ng Excel Online<\/a> \u0111i t\u00ecm hi\u1ec3u nh\u00e9 !<\/p>\n

1. Gi\u00e1 tr\u1ecb tra c\u1ee9u v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1<\/strong><\/p>\n

Xin l\u01b0u \u00fd r\u1eb1ng h\u00e0m VLOOKUP kh\u00f4ng th\u1ec3 t\u00ecm ki\u1ebfm c\u00e1c gi\u00e1 tr\u1ecb c\u00f3 t\u1eeb 256 k\u00fd t\u1ef1 tr\u1edf l\u00ean. N\u1ebfu gi\u00e1 tr\u1ecb tra c\u1ee9u c\u1ee7a b\u1ea1n v\u01b0\u1ee3t qu\u00e1 gi\u1edbi h\u1ea1n n\u00e0y, b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c l\u1ed7i VALUE:<\/p>\n

<\/p>\n

Gi\u1ea3i ph\u00e1p:<\/strong>\u00a0H\u00e3y s\u1eed d\u1ee5ng h\u00e0m INDEX \/ MATCH<\/a> thay th\u1ebf. Trong v\u00ed d\u1ee5 tr\u00ean, h\u00e0m INDEX \/ MATCH ho\u1ea1t \u0111\u1ed9ng ho\u00e0n h\u1ea3o:<\/p>\n

= INDEX (C2: C7, MATCH (TRUE, INDEX (B2: B7 = F$2,0), 0))<\/strong><\/p>\n

2. Kh\u00f4ng c\u00f3 \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 t\u1edbi b\u1ea3ng t\u00ednh tra c\u1ee9u<\/strong>
\nN\u1ebfu b\u1ea1n l\u1ea5y d\u1eef li\u1ec7u t\u1eeb m\u1ed9t b\u1ea3ng t\u00ednh kh\u00e1c, b\u1ea1n ph\u1ea3i bao g\u1ed3m \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 \u0111\u1ebfn t\u1ec7p \u0111\u00f3. Ch\u00ednh x\u00e1c h\u01a1n, b\u1ea1n ph\u1ea3i k\u00e8m theo t\u00ean c\u1ee7a t\u1ec7p (bao g\u1ed3m c\u1ea3 ph\u1ea7n m\u1edf r\u1ed9ng) trong d\u1ea5u ngo\u1eb7c vu\u00f4ng [], v\u00e0 sau \u0111\u00f3 ch\u1ec9 \u0111\u1ecbnh t\u00ean c\u1ee7a trang t\u00ednh v\u00e0 d\u1ea5u ch\u1ea5m than. Ngo\u00e0i ra, b\u1ea1n n\u00ean \u0111\u1eb7t ch\u00fang trong d\u1ea5u nh\u00e1y trong tr\u01b0\u1eddng h\u1ee3p t\u1ec7p ho\u1eb7c t\u00ean trang t\u00ednh c\u00f3 ch\u1ee9a kho\u1ea3ng tr\u1ed1ng. <\/span><\/p>\n

\u0110\u00e2y l\u00e0 c\u1ea5u tr\u00fac c\u00f4ng th\u1ee9c ho\u00e0n ch\u1ec9nh c\u1ee7a vlookup tra c\u1ee9u d\u1eef li\u1ec7u t\u1eeb m\u1ed9t b\u1ea3ng t\u00ednh kh\u00e1c: <\/span><\/p>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

= VLOOKUP (lookup_value, \u2018[t\u00ean t\u1ec7p] t\u00ean trang t\u00ednh\u2019! Table_array, col_index_num, FALSE) <\/strong><\/span><\/p>\n

M\u1ed9t c\u00f4ng th\u1ee9c th\u1ef1c s\u1ef1 c\u00f3 th\u1ec3 tr\u00f4ng nh\u01b0 sau: <\/span><\/p>\n

= VLOOKUP ($A$2, \u2018[New Prices.xls] Sheet1\u2019! $B:$D, 3, FALSE)<\/strong> <\/span><\/p>\n

C\u00f4ng th\u1ee9c tr\u00ean s\u1ebd t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u00f4 A2 trong c\u1ed9t B c\u1ee7a Sheet1 t\u1ec7p \u201cNew Prices<\/strong>\u201d v\u00e0 tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p t\u1ea1i c\u1ed9t D. <\/span><\/p>\n

N\u1ebfu b\u1ea5t k\u1ef3 ph\u1ea7n t\u1eed n\u00e0o c\u1ee7a \u0111\u01b0\u1eddng d\u1eabn b\u1ecb thi\u1ebfu, c\u00f4ng th\u1ee9c VLOOKUP c\u1ee7a b\u1ea1n s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng v\u00e0 tr\u1ea3 l\u1ea1i l\u1ed7i #VALUE (tr\u1eeb khi b\u1ea3ng t\u00ednh tra c\u1ee9u hi\u1ec7n \u0111ang m\u1edf).<\/span><\/p>\n

\n

N\u00ean xem th\u00eam: Th\u00e0nh chuy\u00ean gia Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi n\u00e2ng cao trong 7 ng\u00e0y<\/a><\/p>\n<\/blockquote>\n

3. \u0110\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1<\/strong> <\/span><\/p>\n

Kh\u00f3 c\u00f3 t\u00ecnh hu\u1ed1ng nh\u1eadp m\u1ed9t s\u1ed1 nh\u1ecf h\u01a1n \u201c1\u201d \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh c\u1ed9t gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1. Tuy v\u1eady, \u0111i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 x\u1ea3y ra n\u1ebfu \u0111\u1ed1i s\u1ed1 n\u00e0y l\u00e0 k\u1ebft qu\u1ea3 c\u1ee7a m\u1ed9t h\u00e0m Excel kh\u00e1c \u0111\u01b0\u1ee3c l\u1ed3ng v\u00e0o c\u00f4ng th\u1ee9c Vlookup c\u1ee7a b\u1ea1n. <\/span><\/p>\n

V\u00ec v\u1eady, n\u1ebfu \u0111\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1, c\u00f4ng th\u1ee9c Vlookup c\u1ee7a b\u1ea1n s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE! <\/span><\/p>\n

N\u1ebfu \u0111\u1ed1i s\u1ed1 col_index_num l\u1edbn h\u01a1n s\u1ed1 c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c ch\u1ecdn, c\u00f4ng th\u1ee9c Vlookup tr\u1ea3 v\u1ec1 l\u1ed7i #REF!<\/span><\/p>\n

Ngo\u00e0i ra, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o th\u00eam c\u00e1c b\u00e0i vi\u1ebft chuy\u00ean s\u00e2u h\u01a1n t\u1ea1i c\u00e1c links sau \u0111\u00e2y:<\/p>\n