{"id":285739,"date":"2023-11-18T20:57:54","date_gmt":"2023-11-18T13:57:54","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=285739"},"modified":"2025-12-25T22:14:24","modified_gmt":"2025-12-25T15:14:24","slug":"ham-index","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/ham-index\/","title":{"rendered":"H\u00e0m INDEX trong Excel l\u00e0 g\u00ec? H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng chi ti\u1ebft k\u00e8m v\u00ed d\u1ee5"},"content":{"rendered":"

H\u00e0m INDEX trong Excel l\u00e0 h\u00e0m tra c\u1ee9u tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1ea1i v\u1ecb tr\u00ed c\u1ee5 th\u1ec3 (h\u00e0ng\/c\u1ed9t) trong m\u1ea3ng d\u1eef li\u1ec7u. K\u1ebft h\u1ee3p v\u1edbi MATCH, n\u00f3 thay th\u1ebf ho\u00e0n h\u1ea3o VLOOKUP, linh ho\u1ea1t h\u01a1n v\u00e0 tr\u00e1nh l\u1ed7i khi ch\u00e8n\/x\u00f3a c\u1ed9t. B\u00e0i vi\u1ebft h\u01b0\u1edbng d\u1eabn chi ti\u1ebft c\u00fa ph\u00e1p, v\u00ed d\u1ee5 th\u1ef1c h\u00e0nh (c\u00f3 \u1ea3nh minh h\u1ecda), c\u00e1ch k\u1ebft h\u1ee3p MATCH\/SUMIF\/COUNTIF v\u00e0 x\u1eed l\u00fd l\u1ed7i ph\u1ed5 bi\u1ebfn.<\/span><\/p>\n

Kh\u00e1i ni\u1ec7m v\u00e0 ch\u1ee9c n\u0103ng h\u00e0m INDEX trong Excel<\/b><\/h2>\n

\u00a0H\u00e0m INDEX tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb ho\u1eb7c tham chi\u1ebfu t\u1ea1i giao \u0111i\u1ec3m h\u00e0ng (row<\/span>num) v\u00e0 c\u1ed9t (column<\/span><\/i>num) trong m\u1ea3ng (array). C\u00fa ph\u00e1p c\u01a1 b\u1ea3n: =INDEX(array, row<\/span>num, [column<\/span><\/i>num]).<\/span><\/p>\n

V\u00ed d\u1ee5: =INDEX(A1:C10, 2, 3) tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u00f4 C2.<\/span><\/p>\n

N\u00e2ng cao: =INDEX((A1:B5,D1:E5), 2, 2, 2) ch\u1ecdn m\u1ea3ng th\u1ee9 2.<\/span><\/p>\n

\u01afu \u0111i\u1ec3m: Linh ho\u1ea1t k\u1ebft h\u1ee3p MATCH (thay VLOOKUP), t\u1ef1 \u0111\u1ed9ng c\u1eadp nh\u1eadt d\u1eef li\u1ec7u.<\/span><\/p>\n

\"ham-index-1\"<\/picture><\/p>\n

\u0110\u1ed3ng th\u1eddi, h\u00e0m \u0111\u1ed1i chi\u1ebfu n\u00e0y c\u00f2n c\u00f3 kh\u1ea3 n\u0103ng k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c nh\u01b0 MATCH, SUMIF, COUNTIF v\u00e0 MIN\/MAX. T\u1eeb \u0111\u00f3, t\u1ea1o ra nh\u1eefng c\u00f4ng c\u1ee5 ph\u1ee9c t\u1ea1p \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p to\u00e1n ph\u1ee9c h\u1ee3p nh\u01b0 t\u00ecm ki\u1ebfm nhanh, so s\u00e1nh d\u1eef li\u1ec7u v\u00e0 x\u1eed l\u00fd \u0111i\u1ec1u ki\u1ec7n. V\u1edbi kh\u1ea3 n\u0103ng n\u00e0y, h\u00e0m Excel INDEX tr\u1edf th\u00e0nh m\u1ed9t ph\u1ea7n quan tr\u1ecdng trong quy tr\u00ecnh ph\u00e2n t\u00edch v\u00e0 t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u trong Excel.<\/span><\/p>\n

T\u1ea1i sao n\u00ean s\u1eed d\u1ee5ng h\u00e0m INDEX?<\/b><\/h2>\n

Vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m gi\u00e1 tr\u1ecb INDEX mang l\u1ea1i nhi\u1ec1u \u01b0u \u0111i\u1ec3m m\u00e0 kh\u00f4ng ph\u1ea3i ai c\u0169ng bi\u1ebft \u0111\u1ebfn. \u0110\u1ea7u ti\u00ean, b\u1ea1n c\u1ea7n bi\u1ebft r\u1eb1ng m\u1ed7i h\u00e0m trong Excel \u0111\u1ec1u \u0111\u01b0\u1ee3c t\u00ednh to\u00e1n tr\u01b0\u1edbc v\u1ec1 kh\u1ea3 n\u0103ng \u1ee9ng d\u1ee5ng. \u0110\u1ed1i v\u1edbi INDEX c\u0169ng nh\u01b0 v\u1eady, khi b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m n\u00e0y c\u00f3 th\u1ec3 y\u00ean t\u00e2m v\u1ec1 kh\u1ea3 n\u0103ng x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed ch\u00ednh x\u00e1c c\u1ee7a gi\u00e1 tr\u1ecb trong khu v\u1ef1c \u0111\u01b0\u1ee3c ch\u1ecdn. Nh\u01b0 v\u1eady, k\u1ebft qu\u1ea3 b\u1ea1n nh\u1eadn v\u1ec1 ch\u1eafc ch\u1eafn s\u1ebd \u0111\u00fang th\u00f4ng tin c\u1ea7n thi\u1ebft.<\/span><\/p>\n

Th\u1ee9 hai, t\u00ednh t\u1ef1 \u0111\u1ed9ng c\u1eadp nh\u1eadt c\u1ee7a INDEX c\u0169ng \u0111\u01b0\u1ee3c nhi\u1ec1u \u0111\u00e1nh gi\u00e1 cao. C\u1ee5 th\u1ec3, khi d\u1eef li\u1ec7u trong b\u1ea3ng t\u00ednh thay \u0111\u1ed5i, INDEX s\u1ebd t\u1ef1 \u0111\u1ed9ng c\u1eadp nh\u1eadt gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1, gi\u00fap duy tr\u00ec t\u00ednh \u0111\u1ed3ng nh\u1ea5t gi\u1eefa c\u00f4ng th\u1ee9c v\u00e0 b\u1ea3ng t\u00ednh trong Excel.<\/span><\/p>\n

\"ham-index-2\"<\/picture><\/p>\n

Th\u1ee9 ba, vi\u1ec7c s\u1eed d\u1ee5ng <\/span>h\u00e0m c\u00f4ng th\u1ee9c INDEX c\u00f3 th\u1ec3 gi\u1ea3m s\u1ed1 l\u01b0\u1ee3ng c\u00f4ng th\u1ee9c c\u1ea7n s\u1eed d\u1ee5ng so v\u1edbi c\u00e1ch ti\u1ebfp c\u1eadn truy\u1ec1n th\u1ed1ng. N\u00f3 gi\u00fap b\u1ea3ng t\u00ednh tr\u00f4ng \u0111\u01a1n gi\u1ea3n h\u01a1n, c\u0169ng nh\u01b0 ng\u01b0\u1eddi d\u00f9ng d\u1ec5 d\u00e0ng \u0111\u1ecdc v\u00e0 ph\u00e2n t\u00edch \u0111\u01b0\u1ee3c c\u00e1c th\u00f4ng tin ghi nh\u1eadn \u0111\u01b0\u1ee3c.<\/span><\/p>\n

L\u1ee3i \u00edch th\u1ee9 t\u01b0 c\u1ee7a INDEX, \u0111\u00f3 ch\u00ednh l\u00e0 h\u00e0m n\u00e0y c\u00f3 t\u00ednh t\u01b0\u01a1ng t\u00e1c v\u00f4 c\u00f9ng nhau. Nh\u01b0 th\u00f4ng tin \u1edf ph\u1ea7n kh\u00e1i ni\u1ec7m Ho\u00e0ng H\u00e0 Mobile c\u00f3 chia s\u1ebb l\u00e0 h\u00e0m \u0111\u1ed1i chi\u1ebfu INDEX k\u1ebft h\u1ee3p t\u1ed1t v\u1edbi c\u00e1c h\u00e0m v\u00e0 c\u00f4ng c\u1ee5 kh\u00e1c trong Excel. H\u01a1n n\u1eefa khi k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m, INDEX s\u1ebd gia t\u0103ng t\u00ednh ti\u1ec7n l\u1ee3i v\u00e0 h\u1eefu \u00edch trong qu\u00e1 tr\u00ecnh x\u1eed l\u00fd c\u00e1c d\u1eef li\u1ec7u ph\u1ee9c t\u1ea1p.\u00a0<\/span><\/p>\n

Nh\u00ecn chung, h\u00e0m tr\u00edch xu\u1ea5t INDEX kh\u00f4ng ch\u1ec9 gi\u00fap t\u1ed1i \u01b0u h\u00f3a qu\u00e1 tr\u00ecnh l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u, m\u00e0 c\u00f2n l\u00e0m t\u0103ng kh\u1ea3 n\u0103ng hi\u1ec7u qu\u1ea3 v\u00e0 hi\u1ec7u su\u1ea5t c\u1ee7a c\u00f4ng vi\u1ec7c trong Excel.<\/span><\/p>\n

C\u00f4ng th\u1ee9c c\u1ee7a h\u00e0m INDEX, c\u00f3 v\u00ed d\u1ee5<\/b><\/h2>\n

Hi\u1ec7n nay, ch\u00fang ta s\u1ebd c\u00f3 2 c\u00f4ng th\u1ee9c \u0111\u1ec3 t\u00ednh h\u00e0m tr\u00edch xu\u1ea5t d\u1eef li\u1ec7u INDEX, m\u1ed9t l\u00e0 c\u00f4ng th\u1ee9c \u0111\u01a1n gi\u1ea3n v\u00e0 hai l\u00e0 c\u00f4ng th\u1ee9c n\u00e2ng cao. D\u1ef1a v\u00e0o c\u00e1c th\u00f4ng tin d\u01b0\u1edbi \u0111\u00e2y, b\u1ea1n c\u00f3 th\u1ec3 l\u1ef1a ch\u1ecdn c\u00f4ng th\u1ee9c \u0111\u1ec3 \u00e1p d\u1ee5ng v\u00e0o t\u1eebng t\u00ecnh hu\u1ed1ng c\u1ee5 th\u1ec3 nh\u00e9.<\/span><\/p>\n

C\u00f4ng th\u1ee9c c\u01a1 b\u1ea3n v\u1edbi 1 m\u1ea3ng<\/b><\/h3>\n

C\u00f4ng th\u1ee9c h\u00e0m \u201c<\/span>=INDEX(array, row_num, [column_num])<\/b>\u201d.<\/span><\/p>\n

Trong \u0111\u00f3, array l\u00e0 ph\u1ea1m vi d\u1eef li\u1ec7u m\u00e0 ng\u01b0\u1eddi d\u00f9ng mu\u1ed1n truy c\u1eadp. C\u00f2n row_num l\u00e0 s\u1ed1 h\u00e0ng trong ph\u1ea1m vi b\u1ea1n mu\u1ed1n truy xu\u1ea5t gi\u00e1 tr\u1ecb. \u0110\u1ed3ng th\u1eddi, column_num (t\u00f9y ch\u1ecdn) l\u00e0 s\u1ed1 c\u1ed9t trong ph\u1ea1m vi b\u1ea1n mu\u1ed1n truy xu\u1ea5t gi\u00e1 tr\u1ecb, n\u1ebfu b\u1ea1n b\u1ecf qua ph\u1ea7n n\u00e0y th\u00ec h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 to\u00e0n b\u1ed9 h\u00e0ng t\u1ea1i v\u1ecb tr\u00ed \u0111\u00e3 ch\u1ecdn.<\/span><\/p>\n

V\u00ed d\u1ee5 1:<\/strong> Nh\u1eb1m gi\u00fap cho ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng tr\u1ef1c ti\u1ebfp c\u00f4ng th\u1ee9c, ch\u00fang t\u00f4i s\u1ebd cung c\u1ea5p ngay cho b\u1ea1n m\u1ed9t v\u00ed d\u1ee5 minh h\u1ecda. T\u1ea1i \u0111\u00e2y, b\u00e0i to\u00e1n y\u00eau c\u1ea7u c\u1ea7n \u0111\u1ed1i s\u00e1nh d\u1eef li\u1ec7u c\u1ee7a 2 c\u1ed9t v\u00e0 10 h\u00e0ng (t\u01b0\u01a1ng \u1ee9ng v\u1edbi v\u1ecb tr\u00ed \u00f4 trong Excel l\u00e0 B2:C11). V\u00e0 gi\u1ea3 \u0111\u1ecbnh ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng data t\u1ea1i d\u00f2ng th\u1ee9 3 v\u00e0 c\u1ed9t th\u1ee9 1 trong b\u1ea3ng t\u00ednh.<\/span><\/p>\n

\"ham-index-3\"<\/picture><\/p>\n

Nh\u01b0 v\u1eady, ch\u00fang ta s\u1ebd \u0111\u1eb7t ch\u1ecdn m\u1ed9t \u00f4 m\u00e0 k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1, sau \u0111\u00f3 b\u1ea1n thi\u1ebft l\u1eadp c\u00f4ng th\u1ee9c <\/span>h\u00e0m INDEX<\/b> \u201c<\/span>=INDEX(B2:C11;3;1)<\/b>\u201d v\u00e0 nh\u1ea5n “Enter”. Trong \u0111\u00f3, B2:C11 t\u01b0\u01a1ng \u1ee9ng v\u1edbi ph\u1ea1m vi d\u1eef li\u1ec7u array, 3 l\u00e0 v\u1ecb tr\u00ed c\u1ee7a d\u00f2ng v\u00e0 1 l\u00e0 v\u1ecb tr\u00ed c\u1ee7a c\u1ed9t.\u00a0<\/span><\/p>\n

C\u00f4ng th\u1ee9c n\u00e2ng cao v\u1edbi nhi\u1ec1u m\u1ea3ng<\/b><\/h3>\n

\u0110\u1ed1i v\u1edbi tr\u01b0\u1eddng h\u1ee3p c\u1ea7n s\u1eed d\u1ee5ng tham chi\u1ebfu cho nhi\u1ec1u m\u1ea3ng, ng\u01b0\u1eddi d\u00f9ng c\u1ea7n \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c cao c\u1ea5p h\u01a1n. C\u1ee5 th\u1ec3 \u0111\u00f3 l\u00e0: “<\/span>=INDEX(reference, row_num, column_num, [area_num])<\/b>“.<\/span><\/p>\n

Trong \u0111\u00f3, reference l\u00e0 ph\u1ea1m vi d\u1eef li\u1ec7u m\u00e0 ng\u01b0\u1eddi d\u00f9ng mu\u1ed1n truy c\u1eadp, n\u00f3 c\u00f3 th\u1ec3 l\u00e0 m\u1ed9t d\u00e3y d\u1eef li\u1ec7u, m\u1ed9t b\u1ea3ng t\u00ednh ho\u1eb7c m\u1ed9t m\u1ea3ng. T\u01b0\u01a1ng t\u1ef1 v\u1edbi c\u00f4ng th\u1ee9c c\u01a1 b\u1ea3n, row_num v\u00e0 column_num l\u1ea7n l\u01b0\u1ee3t l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a h\u00e0ng hay c\u1ed9t trong ph\u1ea1m vi b\u1ea1n mu\u1ed1n \u0111\u1ed1i chi\u1ebfu gi\u00e1 tr\u1ecb. C\u00f2n c\u1ea5u tr\u00fac [area_num] d\u00f9ng \u0111\u1ec3 ch\u1ec9 \u0111\u1ecbnh ph\u1ea1m vi c\u1ee5 th\u1ec3 n\u00e0o \u0111\u00f3, n\u1ebfu b\u1ecf qua ph\u1ea7n n\u00e0y th\u00ec n\u00f3 s\u1ebd \u0111\u01b0\u1ee3c m\u1eb7c \u0111\u1ecbnh l\u00e0 ph\u1ea1m vi \u0111\u1ea7u ti\u00ean.<\/span><\/p>\n

V\u00ed d\u1ee5 2:<\/strong> Ch\u00fang ta s\u1ebd t\u1eadn d\u1ee5ng l\u1ea1i b\u00e0i to\u00e1n \u1edf v\u00ed d\u1ee5 1 v\u1ec1 y\u00eau c\u1ea7u c\u1ea7n tr\u00edch xu\u1ea5t d\u1eef li\u1ec7u c\u1ee7a 2 c\u1ed9t v\u00e0 10 h\u00e0ng. Tuy nhi\u00ean, \u0111i\u1ec3m kh\u00e1c bi\u1ec7t \u1edf \u0111\u00e2y l\u00e0 ng\u01b0\u1eddi d\u00f9ng c\u00f3 \u0111\u1ebfn 2 ph\u1ea1m vi th\u00f4ng tin kh\u00e1c nhau l\u00e0 B2:C6 v\u00e0 B8:C12. C\u1ed9ng th\u00eam v\u00e0o \u0111\u00f3, ng\u01b0\u1eddi d\u00f9ng c\u1ea7n l\u1ea5y ra \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb \u1edf h\u00e0ng 4, c\u1ed9t 1 v\u00e0 m\u1ea3ng 2 (B8:C12). V\u1eady l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 \u0111\u00fang nh\u1ea5t.<\/span><\/p>\n

\"ham-index-4\"<\/picture><\/p>\n

\u0110\u1eebng lo, ch\u00fang ta \u0111\u00e3 c\u00f3 c\u00f4ng th\u1ee9c c\u1ee5 th\u1ec3 v\u00e0 b\u1ea1n ch\u1ec9 c\u1ea7n \u00e1p d\u1ee5ng n\u00f3 l\u00e0 \u0111\u01b0\u1ee3c. B\u00e0i gi\u1ea3i <\/span>h\u00e0m Excel INDEX c\u1ee7a ch\u00fang ta nh\u01b0 sau “<\/span>=INDEX((B2:C6;B8:C12);4;1;2)<\/b>“. Trong \u0111\u00f3, B2:C6;B8:C12 l\u00e0 ph\u1ea1m vi d\u1eef li\u1ec7u reference, 4 l\u00e0 h\u00e0ng, 1 l\u00e0 c\u1ed9t v\u00e0 2 l\u00e0 m\u1ea3ng c\u1ea7n l\u1ea5y d\u1eef li\u1ec7u ch\u00ednh x\u00e1c.<\/span><\/p>\n

L\u01b0u \u00fd chung<\/b><\/h3>\n

M\u1eb7c d\u00f9 l\u00e0 c\u00f4ng th\u1ee9c c\u01a1 b\u1ea3n v\u00e0 r\u1ea5t d\u1ec5 \u00e1p d\u1ee5ng, nh\u01b0ng \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 chu\u1ea9n x\u00e1c nh\u1ea5t th\u00ec ng\u01b0\u1eddi d\u00f9ng n\u00ean l\u01b0u \u00fd th\u00eam \u0111\u1ebfn 2 v\u1ea5n \u0111\u1ec1 nh\u01b0 sau.<\/span><\/p>\n