Lm th? no ? s? d?ng VLOOKUP ho?c HLOOKUP ? t?m th?y m?t k?t h?p chnh xc

D?ch tiu ? D?ch tiu ?
ID c?a bi: 181213 - 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

Cc ch?c nng VLOOKUP v HLOOKUP ch?a m?t ?i s? ?c g?i l range_lookup cho php b?n t?m th?y m?t k?t h?p chnh xc ? tra c?u gi tr? c?a b?n m khng c?n phn lo?i b?ng tra c?u.

Chu y N khng ph?i l c?n thi?t ? s?p x?p b?ng tra c?u n?u b?n s? d?ng cc range_lookup ?i s? m?t cch chnh xc.

THNG TIN THM

C php c?a cc ch?c nng ny ?c ?nh ngh?a nh sau.

Ch?c nng VLOOKUP

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
				
ni:
   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   col_index    The column number in the table_array for which the
                matching value should be returned.

   range_lookup It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP finds an exact match. If an
                exact match is not found, the #N/A error value is returned.
				
Chu y N?u range_lookup l ng ho?c b? qua (cho m?t tr?n ?u g?n ng), cc gi tr? trong c?t table_array, ?u tin ph?i ?c s?p x?p theo tng d?n ?t hng. N?u range_lookup l FALSE (cho m?t k?t h?p chnh xc), table_array no khng c?n ph?i ?c s?p x?p.

V d? c s? d?ng sai nh l ?i s? Range_lookup

Danh sch sau y c ch?a m?t s? lo?i tri cy v mu s?c tng ?ng c?a h?. Thng bo r?ng c?t ?u tin khng ?c s?p x?p:
   A1: Fruit    B1: Color  
   A2: Kiwi     B2: Green 
   A3: Banana   B3: Yellow 
   A4: Grape    B4: Purple  
   A5: Apple    B5: Red   
				
Cng th?c sau y th?y mu (mu ?) tng ?ng v?i qu? to. B?n c th? g? cng th?c trong b?t k? t? bo trn b?ng tnh:
=VLOOKUP("Apple",A2:B5,2,FALSE)
				
Nh?n th?y r?ng n?u b?n thay ?i tham s? range_lookup thnh TRUE, Excel tr? v? l?i # N/A, b?i v? c?t ?u tin khng ?c s?p x?p.

Ch?c nng HLOOKUP

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
				
ni:
   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   row_index    The row number in the table_array for which the
                matching value should be returned.

   range_lookup It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP finds an exact match.
                If an exact match is not found, the #N/A error value is
                returned.
				
Chu y N?u range_lookup l ng ho?c b? qua (cho m?t tr?n ?u g?n ng), cc gi tr? trong hng ?u c?a table_array ph?i ?c s?p x?p theo th? t? tng d?n. N?u range_lookup l FALSE (cho m?t k?t h?p chnh xc), table_array khng c?n ph?i ?c s?p x?p.

V d? c s? d?ng sai nh l ?i s? Range_lookup

Danh sch sau y c ch?a m?t s? lo?i tri cy v mu s?c tng ?ng c?a h?. Thng bo r?ng c?t ?u tin khng ?c s?p x?p:
   A1: Fruit    B1: Color
   A2: Kiwi     B2: Green
   A3: Banana   B3: Yellow
   A4: Grape    B4: Purple
   A5: Apple    B5: Red
				
Cng th?c sau y th?y c?t mu, v tr? v? hng (-1) th? ba cho tiu ? mu vng. B?n c th? g? cng th?c trong b?t k? t? bo trn b?ng tnh:
=HLOOKUP("Color",A1:B5,3,FALSE)
				
Nh?n th?y r?ng n?u b?n thay ?i tham s? range_lookup thnh TRUE, Excel tr? v? l?i # N/A, b?i v? c?t ?u tin khng ?c s?p x?p.

THAM KH?O

? bi?t thm chi ti?t v? cch s? d?ng ch?c nng LOOKUP trong Microsoft Excel, b?m vo nh?ng con s? bi vi?t d?i y ? xem cc bi vi?t trong c s? ki?n th?c Microsoft:
181212 XL: Th?c hi?n m?t tra c?u v?i Unsorted d? li?u trong Excel
214264 XL: Lm th? no ? th?c hi?n m?t tra c?u ch?
214069 XL: Lm th? no ? tr? v? ?u tin ho?c cu?i cng tr?n ?u trong m?t m?ng
181201 XL: HLOOKUP, VLOOKUP, tra c?u tr? v? gi tr? khng chnh xc
213476 XL: K?t qu? b?t ng? v?i h?n h?p vn b?n v s? trong Lookup b?ng
280094 XL: M?u ng?i dng ?nh ngh?a ch?c nng ? ?n th?c l?i
? bi?t thm chi ti?t v? cc ch?c nng HLOOKUP, nh?p vo Tr? gip Microsoft Excel trn ccTr giup tr?nh n, lo?i HLOOKUP ch?c nng b?ng tnh trong tr? l? vn ph?ng ho?c thu?t s? tr? l?i, v sau b?m Tim kim ? xem cc ch? ?.
? bi?t thm chi ti?t v? cc ch?c nng VLOOKUP, nh?p vo Tr? gip Microsoft Excel trn ccTr giup tr?nh n, lo?i VLOOKUP ch?c nng b?ng tnh trong tr? l? vn ph?ng ho?c thu?t s? tr? l?i, v sau b?m Tim kim ? xem cc ch? ?.

Thu?c tnh

ID c?a bi: 181213 - L?n xem xt sau cng: 20 Thang Chin 2011 - Xem xt l?i: 3.0
p d?ng
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
  • Microsoft Office Excel 2007
T? kha:
kbhowto kbmt KB181213 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:181213

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