vlook up formula.xlsx

8/16/2019 VLOOK UP FORMULA.xlsx

http://slidepdf.com/reader/full/vlook-up-formulaxlsx 1/31

The column nu

they are part of t

col 1 col 2 col 3 col 4 col 5 col 6

Jan 10 20 30 40 50Feb 80 90 100 110 120Mar 97 69 45 51 77

Type a month to look for : FebWh !h !ol"mn nee#$ to be p !ke# o"t : 4

The re$"lt $ : 100 %&'(()*+,-11./6: 8.-

What Does It Do ? This function scans down the row headings at the side of a table to nd a spWhen the item is found, it then scans across to pick a cell entry.

Syntax=VLOO !"#$temTo%ind,&angeToLook$n,'olumnTo"ick%rom,(ortedOr!nsorted

The $temTo%ind is a single item speci ed by the user. The &angeToLook$n is the range of data with the row headings at the left ha The 'olumnTo"ick%rom is how far across the table the function should look t The (orted*!nsorted is whether the column headings are sorted. T&!+ for y

Formatting-o special formatting is needed.

Example 1 This table is used to nd a alue based on a speci ed name and month. The =VLOO !"#) is used to scan down to nd the name. The problem arises when we need to scan across to nd the month column. To sol e the problem the =/ T'0#) function is used.

The =/ T'0#) looks through the list of names to nd the month we re1uire.

the position of the month in the list. !nfortunately, because the list of montas the lookup range, the =/ T'0#) number is 2 less than we re1uire, so anadded to compensate.

The =VLOO !"#) now uses this =/ T'0#) number to look across the columpicks out the correct cell entry.

The =VLOO !"#) uses % L(+ at the end of the function to indicate to +3celrow headings are not sorted.

Jan Feb Mar ob 10 80 97



r ! 20 90 69 lan 30 100 45/arol 40 110 51

a # 50 120 77

Type a name to look for : er !Type a month to look for : mar

The re$"lt $ : 69 %&'(()*+,F56./50:F54.M T/ ,F57. 49:F49.0 1.F '

Example 2 This e3ample shows how the =VLOO !"#) is used to pick the cost of a spardi4erent makes of cars.

The =VLOO !"#) scans down row headings in column % for the spare part eWhen the make is found, the =VLOO !"#) then scans across to nd the pricresult of the =/ T'0#) function to nd the position of the make of car.

The functions use the absolute ranges indicated by the dollar symbol . Thiswhen the formula is copied to more cells, the ranges for =VLOO !"#) and =not change.

Maker pare /o$t Lookup Table&a" hall ;n t on <50 &a" hall For#

&W -ear o <600 -ear o 500 450

For# n; ne <1.200 n; ne 1000 1200&W teer n; <275 teer n; 250 350For# ;n t on <70 ;n t on 50 70

For# /= ea# <290 /= ea# 300 290

&a" hall -ear o <500For# n; ne <1.200

%&'(()*+,/81.F75: 79.M T/ , 81.-74: 74.0 1.F '

Example 3

$n the following e3ample a builders merchant is o4ering discount on large o The !nit 'ost Table holds the cost of 2 unit of 5rick, Wood and 6lass. The 7iscount Table holds the arious discounts for di4erent 1uantities of ea The Orders Table is used to enter the orders and calculate the Total.

ll the calculations take place in the Orders Table. The name of the $tem is typed in column ' of the Orders Table.

The !nit 'ost of the item is then looked up in the !nit 'ost Table. The % L(+ option has been used at the end of the function to indicate tha

names down the side of the !nit 'ost Table are not sorted. !sing the % L(+ option forces the function to search for an e3act match. $

not found, the function will produce an error.



=VLOO !"#'289,'22:;7229,8,% L(+)

The discount is then looked up in the 7iscount Table$f the <uantity Ordered matches a alue at the side of the 7iscount Table thlook across to nd the correct discount. The T&!+ option has been used at the end of the function to indicate that

down the side of the 7iscount Table are sorted. !sing T&!+ will allow the function to make an appro3imate match. $f the

not match a alue at the side of the 7iscount Table, the ne3t lowest alue Trying to match an order of 28 will drop down to 2>>, and the discount fr

the 2>> row is used. =VLOO !"#7289,%22:;$229,/ T'0#'289,622?;$22?,>)@2,T&!+)

$!o"nt Tabl

*n t /o$t Table Brick Wood

Brick <2 1 0> 0>Wood <1 100 6> 3>

lass <3 300 8> 5>

(r#er$ Tabletem *n t$ *n t /o$t $!o"nt Totalr !k 100 <2 6> <188

Woo# 200 <1 3> <194-la$$ 150 <3 12> <396

r !k 225 <2 6> <423Woo# 50 <1 0> <50-la$$ 500 <3 15> <1.275

%ormula for ;!nit 'ost %&'(()*+,/126./114: 116.2.F '7iscount %&'(()*+, 126.F114: 116.M T/ ,/126.-113: 113.0 1.T?*

Total %, 126@ 126 A, 126@ 126@F126



bers are not needed.

he illustration.

12.F '

eci ed item.

)

d side.o pick from.

es, % L(+ for no.

$t then calculates

hs is not as widee3tra 2 is

s and

hat the



part for

tered in column '.e, using the

nsures that/ T'0#) do

&W

600

80027545

310

ders.

h product.

the product

a match is



e =VLOO !" will

the alues

uantity Ordered doesis used.m

lass

0>12>

15>



LOO !" && ABame Jan Feb Mar

!lan 10 80 "#Bo$ 20 90 %"

&arol 30 100 '(Da)id 40 110 (1Eric 50 120 ##

Francis 60 130 2*

ail 70 140 #3

Type a Bame n th $ !ell : r !

77 %'(()*+,F12. 4:-10

What Does It Do ? This function looks for a piece of information in a list, and then picks an itelast cell in the adBacent row or column.

$t always picks the data from the end of the row or column, so it is no good ito pick data from part way across a list, #use VLOO !" or 0LOO !").

The way in which the function decides whether to pick from the row or coluon the siCe of the table.

trying to nd a match for the pieyou asked it to look for.When a match is found, the funcacross to the right most columnlast entry on the row.

work in Bust the same way as if t

rows than columns, as in the de

to nd a match for the piece of iha e asked it to look for.When a match is found, the funcdown to the bottom cell of the cthe last entry of the column.

Syntax

=LOO !"#WhatToLook%or,&angeToLook$n) The WhatToLook%or should be a single item. The &angeToLook in can be either horiContal or ertical.

The +arch alue for this person is ;

$f the table has more ro,s than columns ; the function will look do,n the le

$f the table has the same amount of rows and columns ;the function will look do,n the le

$f the table has more col-mns than rows ; the function will look across the



5e careful not to include unnecessary heading in the range as these will ca

Example 1 Example 2$n this table there are more $n this table there are more colurows than columns, so the the row heading of Dan is not inclcolumn heading of Dan is lookup range.not included in the lookuprange. lan ob

Jan Jan 100 100

lan 100ob 100

/arol 100a # 100r ! 100

Fre# 100


.ro$lems The list of information to be looked through must be sorted in ascending ordwill occur, either as E-* or incorrect results.

Table 2 shows the -ame column sorted alphabetically, the results of usingbe correct.

Table 8 shows the same data, but not sorted. (ometimes the results will betimes the result will be an E-* error or incorrect gure.

/a$le 1 /a$le 2

Bame Jan Feb Mar Bame Jan

!lan 10 80 "# Da)id 40Bo$ 20 90 %" Eric 50

&arol 30 100 '( !lan 10Da)id 40 110 (1 Bo$ 20

Eric 50 120 ## &arol 30Francis 60 130 2* Francis 60

ail 70 140 #3 ail 70

Bame : r ! Bame : r !

&al"e : 77 &al"e : 45 %'(()*+,/88. 80: 86 %'(()*+,



from the

f you need

n is based

ce of information

tion will lookto pick the

he table had more

cription abo e.

nformation you

tion will then looklumn to pick

ft most column

ft most column and

op row trying



se errors.

ns than rows, souded in the

/arol a #

100 100

er, otherwise errors

LOO !"#) will

orrect, but other

Feb Mar

110 (1120 ##80 "#90 %"

100 '(130 2*

140 #3

88.-80:J86



LOO !" V+'TO&

Bame Jan Feb Mar

!lan 10 *0 97Bo$ 20 "0 69

&arol 30 100 45Da)id 40 110 51Eric 50 120 77

Francis 60 130 28

ail 70 1'0 73

Type a Bame n th $ !ell : r !

120 %'(()*+,F12. 4:-10.F

What Does It Do ? This function looks for a piece of information in a list, and then picks an itea second range of cells.

Syntax=LOO !"#WhatToLook%or,&angeToLook$n,&angeTo"ick%rom)

The WhatToLook%or should be a single item. The &angeToLook in can be either horiContal or ertical. The &angeTo"ick%rom must ha e the same number of cells in it as the &ang5e careful not to include unnecessary heading in the ranges as these will ca


Example The following e3ample shows how the =LOO !"#) function was used to matin cell 6:2 against the list of names in '?F;':?. When a match is found thepicks from the second range +?F;D?F.$f the name 'arol is used, the match is made in the third cell of the list of nathe function picks the third cell from the list of alues.

?an;eTo'ook n ?an;eTo+ !kFrom

lan 5 10 15 20

ob/arol

a # Type a name : /arolr ! &al"e : 15

Fre# %'(()*+,-41./38:/43.

.ro$lems The list of information to be looked through must be sorted in ascending ordwill occur, either as E-* or incorrect results.

The Fe$ alue for this person is ;



:F10

from

ToLookin.use errors.

ch a name typed =LOO !"#) then

mes, and then

25 30

38:J38

er, otherwise errors



0 LOO !"

Jan Feb Mar row 1 The row number

10 80 97 row 2 they are part of t

20 90 69 row 3

30 100 45 row 4

40 110 51 row 5

50 120 77 row 6

Type a month to look for : FebWh !h roC nee#$ to be p !ke# o"t : 4

The re$"lt $ : 100 % '(()*+,F10. 3:F10.

What Does It Do ? This function scans across the column headings at the top of a table to ndWhen the item is found, it then scans down the column to pick a cell entry.

Syntax=0LOO !"#$temTo%ind,&angeToLook$n,&owTo"ick%rom,(ortedOr!nsorted)

The $temTo%ind is a single item speci ed by the user. The &angeToLook$n is the range of data with the column headings at the to The &owTo"ick%rom is how far down the column the function should look to The (orted*!nsorted is whether the column headings are sorted. T&!+ for y


Example 1 This table is used to nd a alue based on a speci ed month and name. The =0LOO !"#) is used to scan across to nd the month. The problem arises when we need to scan down to nd the row adBacent to To sol e the problem the =/ T'0#) function is used.

The =/ T'0#) looks through the list of names to nd the name we re1uire.

the position of the name in the list. !nfortunately, because the list of nameas the lookup range, the =/ T'0#) number is 2 less than we re1uire, so anadded to compensate.

The =0LOO !"#) now uses this =/ T'0#) number to look down the monthpicks out the correct cell entry.

The =0LOO !"#) uses % L(+ at the end of the function to indicate to +3celcolumn headings are not sorted, e en though to us the order of Dan,%eb,/ar

Jan Feb Mar ob 10 80 97

$f they were sorted alphabetically they would ha e read as Feb, an, + ar.



r ! 20 90 69 lan 30 100 45/arol 40 110 51

a # 50 120 77

Type a month to look for : febType a name to look for : alan

The re$"lt $ : 100 % '(()*+,F54. 47:F54.M T/ ,F55./48:/52.0

Example 2 This e3ample shows how the =0LOO !"#) is used to pick the cost of a spardi4erent makes of cars.

The =0LOO !"#) scans the column headings for the make of car speci ed iWhen the make is found, the =0LOO !"#) then looks down the column to tby the =/ T'0#) function, which scans the list of spares for the item speci

The function uses the absolute ranges indicated by the dollar symbol G. Thiwhen the formula is copied to more cells, the ranges for =0LOO !"#) and =not change.

Maker pare /o$t

&a" hall ;n t on <50 &a" hall For#

&W -ear o <600 -ear o 500 450

For# n; ne <1.200 n; ne 1000 1200&W teer n; <275 teer n; 250 350For# ;n t on <70 ;n t on 50 70

For# /= ea# <290 /= ea# 300 290

&a" hall -ear o <500For# n; ne <1.200

% '(()*+, 79.-72: 77.M T/ ,/79.F73:F77.0 1.F '

Example 3

$n the following e3ample a builders merchant is o4ering discount on large o The !nit 'ost Table holds the cost of 2 unit of 5rick, Wood and 6lass. The 7iscount Table holds the arious discounts for di4erent 1uantities of ea The Orders Table is used to enter the orders and calculate the Total.

ll the calculations take place in the Orders Table. The name of the $tem is typed in column '.

The !nit 'ost of the item is then looked up in the !nit 'ost Table. The % L(+ option has been used at the end of the function to indicate tha

names across the top of the !nit 'ost Table are not sorted. !sing the % L(+ option forces the function to search for an e3act match. $

not found, the function will produce an error.



=0LOO !"#'28H,+222;6228,8,% L(+)

The discount is then looked up in the 7iscount Table$f the <uantity Ordered matches a alue at the top of the 7iscount Table thlook down the column to nd the correct discount. The T&!+ option has been used at the end of the function to indicate that

across the top of the 7iscount Table are sorted. !sing T&!+ will allow the function to make an appro3imate match. $f the

not match a alue at the top of the 7iscount Table, the ne3t lowest alue i Trying to match an order of 28 will drop down to 2>>, and the discount fr

the 2>> column is used. =0LOO !"#728H,+22 ;622F,/ T'0#'28H,7229;722F,>)@2,T&!+)

*n t /o$t TableBrick Wood lass

<2 <1 <3

$!o"nt Table1 100 300

Brick 0> 6> 8>Wood 0> 3> 5>

lass 0> 12> 15>

(r#er$ Tabletem *n t$ *n t /o$t $!o"nt Totalr !k 100 <2 6> <188

Woo# 200 <1 3> <194-la$$ 150 <3 12> <396

r !k 225 <2 6> <423Woo# 50 <1 0> <50-la$$ 500 <3 15> <1.275

!nit 'ost % '(()*+,/127. 111:-112.2.F '

7iscount % '(()*+, 127. 115:-118.M T/ ,/127. 116: 118.0 1.T?*



s are not needed.

he illustration.

11.F '

a speci ed item.

.ick from.

es, % L(+ for no.

he name.

$t then calculates

is not as deepe3tra 2 is

olumn and

hat the is correct.



.F '

part for

column 5. e row speci ed

ed in column '.

ensures that/ T'0#) do

&W

600

80027545

310

ders.

h product.

the product

a match is



=0LOO !" will

the alues

uantity Ordered doess used.

m



nary B"mbe! mal D" alent10 2 % B2 /,/4

1 0 % B2 /,/510 2 % B2 /,/611 3 % B2 /,/7

111111111 511 % B2 /,/81111111111 A1 % B2 /,/91111111110 A2 % B2 /,/101111111101 A3 % B2 /,/11

1000000000 A512 % B2 /,/1211111111111 rr:502 % B2 /,/13

What Does It Do ? This function con erts a binary number to decimal.-egati e numbers are represented using twoIsJcomplement notation.

Syntax=5$-87+'#5inary-umber)

The binary number has a limit of ten characters.




'O-V+&T

12 n !m 30E48 %/(B& ?T,/4. 4. 41 m mm 1000 %/(B& ?T,/5. 5. 5

1 y# m %/(B& ?T,/6. 6. 6

3 yr #ay 1095E75 %/(B& ?T,/8. 8. 85 #ay hr 120 %/(B& ?T,/9. 9. 91 hr mn 60 %/(B& ?T,/10. 10. 101 mn $e! 60 %/(B& ?T,/11. 11. 11

What Does It Do ? This function con erts a alue measure in one type of unit, to the same alin a di4erent type of unit, such as $nches to 'entimetres.

Syntax='O-V+&T# mountTo'on ert,!nitTo'on ert%rom,!nitTo'on ertTo)


Example The following table was used by an $mport * +3porting company to con ert tand siCe of packages from old style ! measuring system to +uropean syst

+o"n#$ ("n!e$ ) lo;ram$We ;ht 5 3 2E35301042

%/(B& ?T, 28. lbm . k; /(B& ?T, 28. oGm . k;

Feet n!he$ Metre$e ;ht 12 6 3E81

'en;th 8 3 2E5146W #th 5 2 1E5748

%/(B& ?T, 34. ft . m /(B& ?T, 34. n . m

!$$re)iations This is a list of all the possible abbre iations which can be used to denote m

Weight +ass Distance-ram ; Meter m) lo;ram k; tat"te m le m

l"; $; Ba"t !al m le Bm+o"n# ma$$ lbm n!h n* ,atom ! ma " Foot ft("n!e ma$$ oGm =ar# y#

n;$trom an;/ime + !a ,1H72 nE + !a

mo"ntTo /on ert

/on ert n;From

/on ert n;To

/on erte# mo"nt



=ear yr ay #ay .ress-reo"r hr +a$!al +a

M n"te mn tmo$phere atme!on# $e! mm of Mer!" mm ;

/emperat-re i -ide;ree /el$ / Tea$poon t$pe;ree Fahr F Table$poon tb$e;ree )el ) Fl" # o"n!e oG

/"p !"pForce + nt ptBeCton B I"art Dt

yne #yn -allon ;al+o"n# for!e lbf ' ter l

Energy .o,er Jo"le J or$epoCer +

r; e Watt W

!T !alor e !al +agnetismle!tron olt e& Te$la Tor$epoCerA +h -a"$$ ;a

WattAho"r Wh

FootApo"n# flbT* T*

These characters can be used as a pre 3 to access further units of measur

+ref M"lt pl er bbre at on +ref M"lt pl er bbre at one a 1E00 18 #e! 1E00 A01 #peta 1E00 15 + !ent 1E00 A02 !

tera 1E00 12 T m ll 1E00 A03 m; ;a 1E00 09 - m !ro 1E00 A06 "me;a 1E00 06 M nano 1E00 A09 nk lo 1E00 03 k p !o 1E00 A12 phe!to 1E00 02 h femto 1E00 A15 f #ekao 1E00 01 e atto 1E00 A18 a

Thermo#ynam !!alor e

!sing K c K as a pre 3 to meters K m K will allow centimetres K cm K to be calcula



e e3pressed 28>

he weight m.

easuring systems.



.ed.



p?.2:2 ? ="$#)

What 7oes $t 7o M This function is e1ual to the alue of "i.$t is correct to 2 decimal places.$t does not need any input, it is a self contained function.

(ynta3="$#)

%ormatting-o special formatting is needed.

+3ample To calculate the area of a circle.

&adius reaHF. ? F8 ="$#)N#'82 8)

8 2 9?.:



B"mber +oCer ?e$"lt3 5 243 %+(W ?,/4. 4

16 2 256 %+(W ?,/5. 55 2 25 %+(W ?,/6. 6

10 3 1000 %+(W ?,/7. 7

What Does It Do ? This function raises a number to a user speci ed power.$t is the same as using the operator, such as ? :, which result is F2.5oth the "OW+&#) function and the operator are the same as using ?N?N?

Syntax="OW+&#-umberTo5e&aised,"ower)


Example To calculate the area of a circle.

?a# "$ rea10 314E16 %+ , @+(W ?,/22.225 1963E50



?.



S4BS/I/4/E

(r ; nal Te t / F / hello

/ / / hello

Borthern ?e; on ?e; on reaan# an# /ement an#

(r ; nal Te t / / / / helloan# an# /ement an#

What Does It Do ? This function replaces a speci ed piece of te3$t can either replace all occurrences of the te3

The function is case sensiti e.

Syntax=(!5(T$T!T+#OriginalTe3t,Te3tTo&emo e,Te3

The $nstanceTo!se is optional, if it is omitted


5ote To cope with upper or lower case in the substisuch as =!""+&#), =LOW+&#) or ="&O"+&#) t

Table 2 shows how di4ering te3t cases alter t

Table 2

(r ; nal Te tBorthern ?e; on ?e; on rea

?e; on reaBorthern ?e; on region reaBorthern ?e; on ?e; on areaBorthern ?e; on region area

Table 8 shows how the ="&O"+&#) function h

(l# Te tTo ?emo e

BeC Te tTo n$ert

(l# Te tTo ?emo e

BeC Te tTo n$ert

(l# Te tTo ?emo e

BeC Te tTo n$ert

Borthern region



Table 8

(r ; nal Te tBorthern ?e; on ?e; on rea

?e; on rea

Borthern ?e; on region reaBorthern ?e; on ?e; on areaBorthern ?e; on region area

% *

(l# Te tTo ?emo e

BeC Te tTo n$ert

Borthern region



*p#ate# Te t hello F % * T T*T , 4./4. 4

hello hello % * T T*T , 5./5. 5

Borthern rea % * T T*T , 6./6. 6 /ement % * T T*T , 7./7. 7

*p#ate# Te t3 / /hello2 an# /ement

% * T T*T , 10./10. 10. 10

% * T T*T , 11./11. 11. 11

with a di4erent piece of te3t.t, or a speci c instance.

To$nsert,$nstanceTo!se) ll instances will be substituted.

tution you can use other te3t functions ensure that the substitution will take place.

e result of the substitution.

*p#ate# Te tBorthern rea

Borthern re; onBorthern ?e; on

Borthern areaBorthern ?e; on

% * T T*T , 39./39. 39

s been used to take account of the mi3ed cases.

n$tan!e Toe ?epla!e#



*p#ate# Te tBorthern reaBorthern rea

Borthern reaBorthern reaBorthern rea

T T*T ,+?(+ ?, 50 .+?(+ ?,/50 .+?(+ ?, 50

vlook up formula.xlsx

Documents