Lab11 Match and Lookup

Document Sample
Lab11 Match and Lookup Powered By Docstoc
					Auditors frequently compare data from different sources to verify the existe
non-existence of a match. For example, the auditor will develop a list of RE
PARTIES ( companies owned by the relatives of the principal owner of the cl
company). Then the auditor will want to determine all sales or purchase
transactions with these individuals. Another example would be a list of term
employees compared to a list of all employees paid in the most recent payr

Two Excel functions aid the auditor in identifying matches. THe first functio
MATCH function and the other is the VLOOKUP.
Returns the relative position of an item in an array that matches a specified
in a specified order. Use MATCH instead of one of the LOOKUP functions wh
need the position of an item in a range instead of the item itself.

 If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed
order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be pla
descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

Searches for a value in the first column of a table array and returns a value in the sam
from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your
comparison values are located in a column to the left of the data that you want to fin

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approxima

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest va
less than lookup_value is returned.
The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may no
correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and
Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do n
be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the firs
found is used. If an exact match is not found, the error value #N/A is returned.
verify the existence or
elop a list of RELATED
owner of the client
 or purchase
 be a list of terminated
ost recent payroll

THe first function is the

ches a specified value
UP functions when you                  example: Match the value        7.2

                                       TABLE ARRAY 1:                        Table array 2:
                                                                   5                           12
                                                                   6                            8
                                                                   8                          7.5
                                                                  12                            5
up_array must be placed in ascending
                                       Match_type=1                          Match_type=1           Table array must by in asc
                                                                  2                #N/A
ay can be in any order.                Match_type=0                          Match_type=0           Table array can be in any
                                                      #N/A                         #N/A
 ookup_array must be placed in
                                       Match_Type=-1                         Match_Type=-1          Table array must be in de
                                                   #N/A                                        3

a value in the same row

OKUP when your
at you want to find.                   lookup:                         5.5
                                                  TABLE ARRAY:
                                                                   5   red
                                                                   6   yellow
                                                                   8   blue
                                                                  12   green
match or an approximate match:
                                               Column Index set to 1
 nd, the next largest value that is   range_lookup argument omitted             5
                                      range_lookup argument set to
wise, VLOOKUP may not give the        true or 1                                 5
                                      range_lookup argument set to
om the Data menu and selecting
                                      false or 0                         #N/A

mn of table_array do not need to
                                              col index set to 2
e lookup_value, the first value
                                      range_lookup argument omitted    red
                                      range_lookup argument set to
                                      true or 1                        red
                                      range_lookup argument set to
                                      false or 0                         #N/A
Table array must by in ascending order

Table array can be in any order

Table array must be in descending order
                   of Exact
Lookup Values      Match                          Item name
Simon, Carly                  2               1   Johnson, Jack
Sawyer, Rebecca      #N/A                     2   Simon, Carly
grateful dead                7                3   Jackson, Michael
Clapton, Eric               10                4     John, Elton
Morrison, Van               13                5   Jackson, Michael
Jackson, Michael             3                6   Rolling Stones
Jackson, Janet               8                7   Grateful Dead
Murphy, Michael              9                8   Jackson, Janet
Henley, Don                 12                9   Murphy, Michael
Rolling Stones               6               10   Clapton, Eric
John, Elton          #N/A                    11   Turner, Tina
Turner, Tina                11               12   Henley, Don
morrison, van               13               13   Morrison, Van

                   Value in Vlookup:Not
                   2nd        an Exact
Lookup Values      Column     Match               Item name
Simon, Carly              296          296    1     John, Elton
Sawyer, Rebecca       #N/A              56    2   Clapton, Eric
grateful dead             560          560    3   Grateful Dead
Clapton, Eric             570          570    4   Henley, Don
Morrison, Van             100          100    5   Jackson, Janet
Jackson, Michael          130          230    6   Jackson, Michael
Jackson, Janet            300          300    7   Jackson, Michael
Murphy, Michael            80           80    8   Johnson, Jack
Henley, Don               460          460    9   Morrison, Van
Rolling Stones             56           56   10   Murphy, Michael
John, Elton           #N/A             230   11   Rolling Stones
Turner, Tina          450000        450000   12   Simon, Carly
morrison, van             100          100   13   Turner, Tina
   Lookup Array


     $300.00      Table Array
Compare the employee names on the Employee Time Sheet with the Employee Names
Master File. (Match employee time sheet name to the Employee Master File). Use the
"Employee MAster FIle Row #" to match the names.
Use the VLOOKUP function to add the employee ID beside the appropriate name on the

Verify that your formulas are working properly. For any lookup or match that did not find
value existed, comment as to why the formula did not work properly.

Upload the file to Entropy
with the Employee Names on the Employee
yee Master File). Use the column labeled

e appropriate name on the Employee Time

p or match that did not find a value when the
                                                  Lab 4                                  RSS Solution

                           Employee Master File   Employee ID # from Master
     Employee Name   Hours Match Row#             File                        Comments
Corbett, Whitney        40
Blalock, Siobhan        60
Beavers, Shantell       52
Jernigan, Ginny         40
Walker, Emily           40
Walker, Elizabeth       40
Vaughn, David           40
Swinnie, Dana           40
Speck, Dana             40
Parrish, Bradley        45
O'Briant, Ashley        41
Moree, Ashley           38
Moran, Anne             40
Whitley, Alexander      40
Wiggs, Alan             40
LNAME        FNAME        NAME                   EMPID
Adriaansen   Samantha     Adriaansen, Samantha           850152772
Averette     Sandi        Averette, Sandi                850217859
Barnes       Scott        Barnes, Scott                  850116578
Beavers      Shantell     Beavers, Shantell              850094802
Boehm        Stephanie    Boehm, Stephanie               850096610
Bouknight    Teri         Bouknight, Teri                850154421
Brammer      Timothy      Brammer, Timothy               850102215
Buckles      Tremauri     Buckles, Tremauri              850089401
Cashmere     Tyrone       Cashmere, Tyrone               850224982
Cook         Wendy        Cook, Wendy                    850114694
Corbett      Whitney      Corbett, Whitney               850226331
Cross        Larry        Cross, Larry                   850092051
Currin       Laura Beth   Currin, Laura Beth             850108842
Dehart       Lauren       Dehart, Lauren                 850007133
Dimmick      Lindsey      Dimmick, Lindsey               850221162
Earney       Lora         Earney, Lora                   850082756
Ebron        Matthew      Ebron, Matthew                 850229189
Evert        Megan        Evert, Megan                   850114984
Fry          Michael      Fry, Michael                   850101748
Gallo        Michael      Gallo, Michael                 850083986
Glazener     Oliver       Glazener, Oliver               850224905
Greene       Rachel       Greene, Rachel                 850105472
Halloran     Richard      Halloran, Richard              850154030
Harmsen      Kent         Harmsen, Kent                  850076054
Hartman      Keith        Hartman, Keith                 850084732
Harvey       Joshua       Harvey, Joshua                 850006749
Haymond      Allyson      Haymond, Allyson               850214224
Hoxie        Emily        Hoxie, Emily                   850152880
Jernigan     Ginny        Jernigan, Ginny                850155786
Kaczmarski   Heather      Kaczmarski, Heather            850090822
Kelly        Ian          Kelly, Ian                     850229182
Kuegel       Jacqueline   Kuegel, Jacqueline             850120879
Kurt         Jarod        Kurt, Jarod                    850116503
Leggett      Jennifer     Leggett, Jennifer              850089838
Liston       John         Liston, John                   850025730
Loftin       John         Loftin, John                   850153237
McQueen      Joshua       McQueen, Joshua                850229190
Meade        Amy          Meade, Amy                     850085214
Moran        Anne         Moran, Anne                    850219071
O'Briant     Ashley       O'Briant, Ashley               850115555
Overby       Billy        Overby, Billy                  850230587
Parrish      Bradley      Parrish, Bradley               850006330
Piver        Brandon      Piver, Brandon                 850099514
Pope         Brandon      Pope, Brandon                  850012181
Reynolds     Brenna       Reynolds, Brenna               850098106
Richardson   Britney      Richardson, Britney            850025436
Shearin      Candace      Shearin, Candace               850000650
Shuford      Chiemi       Shuford, Chiemi                850109623
Smith        Cristen      Smith, Cristen                 850103854
Speck        Dana          Speck, Dana                   850221840
Swinnie    Dana        Swinnie, Dana        850095575
Walker     David       Walker, David        850053889
Walker     Elizabeth   Walker, Elizabeth    850097998
Westcott   Alicia      Westcott, Alicia     850056064
Whitley    Alexander   Whitley, Alexander   850117723
Wiggs      Alan        Wiggs, Alan          850084322

Shared By: