$B%(%/%;%k(B $B%j%9%H(B03
$B%(%/%;%k$NM}2r%7%j!<%:(B
$B$=$N(B3
Functions
$B4X?t(B

LOOKUP()
$B8!:w(B

The LOOKUP function finds a value in a single row or column and matches it with a value in the same position in a different row or column.
LOOKUP $B4X?t$O!"C10l$N9T$^$?$ONs$NCM$r8!:w$7!"JL$N9T$^$?$ONs$NF1$80LCV$K$"$kCM$rJV$7$^$9!#(B

The following is an example of LOOKUP formula syntax:
LOOKUP $B4X?t$N9=J8$r0J2<$K<($7$^$9!#(B

=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)
=LOOKUP($B8!::CM(B,$B8!::HO0O(B,$BBP1~HO0O(B)

The following formula finds $B6LG,(B's age in the sample worksheet:
$B0J2<$N<0$G$O!"6LG,(B $B$NG/Np$r%5%s%W%k$N%o!<%/%7!<%H$+$i8!:w$7$^$9!#(B

=LOOKUP(E2,A2:A5,C2:C5)

The formula uses the value "$B6LG,(B" in cell E2 and finds "$B6LG,(B" in the lookup vector (column A).
$B$3$N<0$G$O!"%;%k(B E2 $B$NCM(B "$B6LG,(B" $B$r;HMQ$7$F!"(B"$B6LG,(B" $B$r8!::HO0O(B ($BNs(B A) $B$G8!:w$7$^$9!#(B
The formula then matches the value in the same row in the result vector (column C).
$B ($BNs(B C) $B$NF1$89T$NCM$,JV$5$l$^$9!#(B
Because "$B6LG,(B" is in row 4, LOOKUP returns the value from row 4 in column C (22).
 "$B6LG,(B" $B$O9T(B 4 $B$K$"$k$?$a!"(BLOOKUP $B4X?t$O9T(B 4 $B$NNs(B C (22) $B$rJV$7$^$9!#(B

Note The LOOKUP function requires that the table be sorted.
$BCm(B : LOOKUP $B4X?t$G$O!"%F!<%V%k$rJB$YBX$($F$*$/I,MW$,$"$j$^$9!#(B

VLOOKUP()
$B=DJ}8~8!:w(B

The VLOOKUP or Vertical Lookup function is used when data is listed in columns.
VLOOKUP ($B=DJ}8~(B (vertical) $B$N8!:w$r0UL#$7$^$9(B) $B4X?t$O!"%G!<%?$,J#?tNs$K4^$^$l$k>l9g$K;HMQ$7$^$9!#(B
This function searches for a value in the left-most column and matches it with data in a specified column in the same row.
$B$3$N4X?t$O!":8C<$NNs$GCM$r8!:w$7!";XDj$5$l$?Ns$K$"$kCM$rJV$7$^$9!#(B
You can use VLOOKUP to find data in a sorted or unsorted table.
VLOOKUP $B$G$O!"%F!<%V%k$N%G!<%?$,JB$SBX$($i$l$F$$$J$/$F$b8!:w$G$-$^$9!#(B
The following example uses a table with unsorted data.
$B0J2<$NNc$G$O!"%G!<%?$,JB$SBX$($i$l$F$$$J$$%F!<%V%k$r;HMQ$7$^$9!#(B

The following is an example of VLOOKUP formula syntax:
VLOOKUP $B4X?t$N9=J8$r0J2<$K<($7$^$9!#(B

=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)
=VLOOKUP($B8!:wCM(B,$BHO0O(B,$BNsHV9f(B,$B8!:w$N7?(B)

The following formula finds $B6LG,(B's age in the sample worksheet:
$B0J2<$N<0$G$O!"6LG,(B $B$NG/Np$r%5%s%W%k$N%o!<%/%7!<%H$+$i8!:w$7$^$9!#(B

=VLOOKUP(E2,A2:C5,3,FALSE)

The formula uses the value "$B6LG,(B" in cell E2 and finds "$B6LG,(B" in the left-most column (column A).
$B$3$N<0$O!"%;%k(B E2 $B$NCM(B "$B6LG,(B" $B$r;HMQ$7$F!"(B"$B6LG,(B" $B$r:8C<$NNs(B ($BNs(B A) $B$G8!:w$7$^$9!#(B
The formula then matches the value in the same row in Column_Index.
$B Column_Index $B$K$"$kCM$,JV$5$l$^$9!#(B
This example uses "3" as the Column_Index (column C).
$B$3$NNc$G$O!"(BColumn_Index $B$H$7$F(B "3" ($BNs(B C) $B$,;HMQ$5$l$F$$$^$9!#(B
Because "$B6LG,(B" is in row 4, VLOOKUP returns the value from row 4 in column C (22).
"$B6LG,(B" $B$O9T(B 4 $B$K$"$k$?$a!"(BVLOOKUP $B4X?t$O!"Ns(B C (22) $B$N9T(B 4 $B$NCM$rJV$7$^$9!#(B

INDEX() and MATCH()

You can use the INDEX and MATCH functions together to get the same results as using LOOKUP or VLOOKUP.
INDEX $B$H(B MATCH $B4X?t$rAH$_9g$o$;$F;HMQ$9$k$H!"(BLOOKUP $B$^$?$O(B VLOOKUP $B$r;HMQ$7$?>l9g$HF10l$N7k2L$rF@$k$3$H$,$G$-$^$9!#(B

The following is an example of the syntax that combines INDEX and MATCH to produce the same results as LOOKUP and VLOOKUP in the previous examples:
INDEX $B$H(B MATCH $B$rAH$_9g$o$;$F!"A0=P$NNc$N(B LOOKUP $B$d(B VLOOKUP $B$HF1$87k2L$rF@$kNc$r0J2<$K<($7$^$9!#(B

=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)
=INDEX($BG[Ns(B,$B9THV9f(B,$BNsHV9f(B)
MATCH($B8!::CM(B,$B8!::HO0O(B,$B>H9g$N7?(B)

The following formula finds $B6LG,(B's age in the sample worksheet:
$B0J2<$N<0$G$O!"6LG,(B $B$NG/Np$r%5%s%W%k$N%o!<%/%7!<%H$+$i8!:w$7$^$9!#(B

=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)

The formula uses the value "$B6LG,(B" in cell E2and finds "$B6LG,(B" in column A.
$B$3$N<0$O!"%;%k(B E2 $B$NCM(B "$B6LG,(B" $B$r;HMQ$7$F!"(B"$B6LG,(B" $B$rNs(B A $B$G8!:w$7$^$9!#(B
It then matches the value in the same row in column C.
$B C $B$K$"$kCM$rA*$S=P$7$^$9!#(B
Because "$B6LG,(B" is in row 4, the formula returns the value from row 4 in column C (22).
 "$B6LG,(B" $B$O9T(B 4 $B$K$"$k$?$a!"$3$N<0$O!"9T(B 4 $B$NNs(B C (22) $B$rJV$7$^$9!#(B

Note If none of the cells in Lookup_Array match Lookup_Value ("$B6LG,(B"), this formula will return #N/A.
$BCm(B : Lookup_Array $BFb$N%;%k$G(B Lookup_Value ("$B6LG,(B") $B$K0lCW$9$kCM$,$J$$>l9g$O!"$3$N<0$+$i$O(B #N/A $B$,JV$5$l$^$9!#(B

OFFSET() and MATCH()

You can use the OFFSET and MATCH functions together to produce the same results as the functions in the previous example.
OFFSET $B$H(B MATCH $B4X?t$r0l=o$K;HMQ$9$k$H!"A0=P$NNc$G@bL@$7$?4X?t$HF10l$N7k2L$rF@$k$3$H$,$G$-$^$9!#(B

The following is an example of syntax that combines OFFSET and MATCH to produce the same results as LOOKUP and VLOOKUP:
OFFSET $B$H(B MATCH $B$rAH$_9g$o$;$F!"A0=P$NNc$N(B LOOKUP $B$d(B VLOOKUP $B$HF1$87k2L$rF@$kNc$r0J2<$K<($7$^$9!#(B

=OFFSET(top_cell,MATCH(Lookup_Value,Lookup_Array,0),Offset_Col)
OFFSET($B4p=`(B,$B9T?t(B,$BNs?t(B,$B9b$5(B,$BI}(B)

MATCH($B8!::CM(B,$B8!::HO0O(B,$B>H9g$N7?(B)

This formula finds $B6LG,(B's age in the sample worksheet:
$B0J2<$N<0$O!"6LG,(B $B$NG/Np$r%5%s%W%k$N%o!<%/%7!<%H$+$i8!:w$9$k$b$N$G$9!#(B

=OFFSET(A1,MATCH(E2,A2:A5,0),2)

The formula uses the value "$B6LG,(B" in cell E2 and finds "$B6LG,(B" in column A.
$B$3$N<0$O!"%;%k(B E2 $B$NCM(B "$B6LG,(B" $B$r;HMQ$7$F!"(B"$B6LG,(B" $B$rNs(B A $B$G8!:w$7$^$9!#(B
The formula then matches the value in the same row but two columns to the right (column C).
$B
Because "$B6LG,(B" is in column A, the formula returns the value in row 4 in column C (22).
 "$B6LG,(B" $B$ONs(B A $B$K$"$k$?$a!"$3$N<0$O9T(B 4 $B$NNs(B C (22) $B$rJV$7$^$9!#(B


HOME$B$KLa$k(B                $B$=$N#2$X(B              $B$=$N#4$X(B