Cach s dung ham LOOKUP trong Excel

D?ch tiu ? D?ch tiu ?
ID c?a bi: 324986 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Tm t?t

Bai vit nay m ta cach s dung ham LOOKUP trong trang tinh Microsoft Excel.

Thng tin thm

Ham LOOKUP tra lai gia tri t dai mt hang hoc mt ct hoc t mt mang. Ham LOOKUP co hai dang cu phap: vec-t v m?ng. Dang vec-t cua LOOKUP tim kim gia tri trong dai mt hang hoc mt ct (c goi la vec-t) ri tra lai gia tri t cung vi tri dai mt hang hoc mt ct th hai. Dang mang cua LOOKUP tim kim gia tri cu th trong hang hoc ct u tin cua mt mang, ri tra lai gia tri t cung vi tri trong hang hoc ct cui cung cua mang.

Dang vec-t cua LOOKUP

Dang vec-t cua LOOKUP tim kim gia tri trong dai mt hang hoc mt ct (c goi la vec-t), ri tra lai gia tri t cung vi tri trong dai mt hang hoc mt ct th hai. S dung dang ham LOOKUP nay khi ban mun xac inh dai cha gia tri ban mun khp.

Cu phap cho Dang Vec-t

LOOKUP(lookup_value,lookup_vector,result_vector)
  • Lookup_value la gia tri ma LOOKUP tim kim trong vec-t u tin. Lookup_value co th la s, vn ban, gia tri l-gic hoc mt tn hay tham chiu chi n mt gia tri.
  • Lookup_vector la mt dai chi cha mt hang hoc mt ct. Gia tri trong lookup_vector co th la vn ban, s hoc gia tri l-gic.

    Quan trong Gia tri trong lookup_vector phai c t theo th t tng dn. Vi du: -2, -1, 0, 1, 2 hoc A-Z hoc FALSE, TRUE. Nu ban khng lam nh vy, LOOKUP co th khng a ra gia tri chinh xac. Vn ban ch hoa va ch thng la tng ng.
  • Result_vector la mt dai chi cha mt hang hoc ct. No phai co kich thc tng t nh lookup_vector.
Chu y
  • Nu LOOKUP khng th tim thy lookup_value, no se khp gia tri ln nht trong lookup_vector ma nho hn hoc bng lookup_value.
  • Nu lookup_value nho hn gia tri nho nht trong lookup_vector, LOOKUP a ra gia tri li #N/A.
Vi du
Thu g?n b?ng nyBung r?ng b?ng ny
AB
1T?n s?Mau sc
24.14o
34.19cam
45.17vng
55.77luc
66.39lam
Thu g?n b?ng nyBung r?ng b?ng ny
Cng th?cM ta (Kt qua)
=LOOKUP(4,91,A2:A6,B2:B6)Tra cu 4,19 trong ct A va tra lai gia tri t ct B cung hang (cam).
=LOOKUP(5,00,A2:A6,B2:B6)Tra cu 5,00 trong ct A va tra lai gia tri t ct B cung hang (cam).
=LOOKUP(7,66,A2:A6,B2:B6)Tra cu 7,66 trong ct A, khp gia tri nho nht tip theo (6,39) va tra lai gia tri t ct B cung hang (lam).
=LOOKUP(0,A2:A6,B2:B6)Tra cu 0 trong ct A va tra lai li vi 0 nho hn gia tri nho nht trong lookup_vector A2:A7 (#N/A).

Dang mang cua LOOKUP

Dang mang cua LOOKUP tim kim gia tri ma ban xac inh trong hang hoc ct u tin cua mt mang, ri tra lai gia tri t cung vi tri trong hang hoc ct cui cung cua mang. S dung dang LOOKUP nay khi cac gia tri ban mun khp hang hoc ct u tin cua mang.

Cu phap cho Biu mu Mang

LOOKUP(lookup_value,array)
  • Lookup_value la gia tri ma LOOKUP tim kim trong mt mang. Lookup_value co th la s, vn ban, gia tri l-gic hoc mt tn hay tham chiu chi n mt gia tri.
    • Nu LOOKUP khng th tim thy lookup_value, no s dung gia tri ln nht trong mang ma nho hn hoc bng lookup_value.
    • Nu lookup_value nho hn gia tri nho nht trong hang hoc ct u tin (tuy thuc vao kich thc mang), LOOKUP tra lai gia tri li #N/A.
  • Array la mt dai cha vn ban, s hoc gia tri l-gic ma ban mun so sanh vi lookup_value.

    Dang mang cua LOOKUP tng t nh ham HLOOKUP va VLOOKUP. S khac bit la HLOOKUP tim kim lookup_value trong hang u tin, VLOOKUP tim kim trong ct u tin va LOOKUP tim kim theo kich thc cua array.
    • Nu array bao gm mt khu vc rng hn chiu cao cua mang (nhiu ct hn hang), LOOKUP tim kim lookup_value trong hang u tin.
    • Nu array hinh vung hoc cao hn chiu rng cua mang (nhiu hang hn ct), LOOKUP tim kim trong ct u tin.
    • Vi HLOOKUP va VLOOKUP, ban co th chi muc xung hoc ngang, nhng LOOKUP lun chon gia tri cui cung trong hang hoc ct.
    Quan trong Gia tri trong array phai c t theo th t tng dn. Vi du: -2, -1, 0, 1, 2 hoc A-Z hoc FALSE, TRUE. Nu ban khng lam nh vy, LOOKUP co th khng a ra gia tri chinh xac. Vn ban ch hoa va ch thng la tng ng.
Vi du
Thu g?n b?ng nyBung r?ng b?ng ny
AB
1a1
2b2
3c3
4d4
Thu g?n b?ng nyBung r?ng b?ng ny
Cng th?cM ta (Kt qua)
=LOOKUP("c",A1:B4)Tra cu "C" trong hang u tin cua mang va tra lai gia tri trong hang cui cung cung ct (3).
=LOOKUP("bump",A1:B4)Tra cu "bump" trong hang u tin cua mang va tra lai gia tri trong ct cui cung cung hang (2).

Thu?c tnh

ID c?a bi: 324986 - L?n xem xt sau cng: 02 Thang Mi Hai 2013 - Xem xt l?i: 7.0
p d?ng
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
T? kha:
kbfunctions kbhowto KB324986

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com