VLOOKUP function without formulas

Document Sample
VLOOKUP function without formulas Powered By Docstoc
					                                                       Help Notes:
VLOOKUP: Looks at a cell, then takes that value and goes over to a look up table where it looks in the first column, and
 when it finds the corresponding value in the first columns, it moves to the left in that row and retrieves a value from
                               one of the table's columns and brings it back to the cell.
Arguments                                                         Descriptions
                     The data you want to look up. This value can be a number, text, a logical value, or a name or cell
lookup_value                                        reference that refers to a value.

                      The lookup table. The first column must have the values to look up such as part number, part
                   name, tax bracket wage figures, grade number intervals. The other columns can have data that you
table_array                   want to retrieve such as part price, tax rates, tax paid from earlier brackets.
                   The number of the columns containing the data you want to retrieve. The number 1 indicates that
col_index_num                               first column; 2 indicates the second column, etc.
                    The type of lookup you want to perform: TRUE or FALSE or 1 or 0. With TRUE or 1 (the default),
                    the VLOOKUP function finds the greatest value that does not exceed the lookup_value value .
                     When the lookup type is TRUE or 1, the first column must be sorted in ascending order, otherwise
                     the VLOOKUP might not retrieve the correct value. With FALSE or 0, the VLOOKUP looks for an
                    exact match of the lookup_value. Instead of TRUE you can use 1 or omitted. Instead of FALSE you
range_lookup                                                can use 0 (zero).
                                  Succeeding in Business with Microsoft Excel notes:
      steps                                                VLOOKUP Algorithm
                   Looks for an exact match. If duplicate values it ignores the second value. If no match it goes to step
                 1                                                    2

                    If the lookup_value is smaller than the first value in the first column, #N/A error is displayed. If the
                 2                value is greater than the first value in the first column, it goes to step 3
                    The VLOOKUP function continues checking until it sees a value bigger than the lookup_value, then
                 3                       it uses the previous row and retrieves a value from that row.
                   It looks to see if the next value is the last value in the table, if it is it retrieves a value from this row,
                 4                                         otherwise it repeats step 3
                                                      excelisfun notes:
                                             The V in VLOOKUP means Vertical.
                VLOOKUP function returns something to a cell or formula that it found in a table_array

 When looking up an exact match (FALSE or 0), it looks through the 1st column of the table_array and finds an exact
         match. If there are duplicates, it finds the first one only. If it can not find a match it returns #N/A

    When looking up an approximate match (TRUE or 1 or omitted)there are two ways to think about how it works:
     1) The VLOOKUP finds the biggest value in the 1st column of the table_array that is greater than or equal to the
                                                           lookup_value
 2) The VLOOKUP takes the lookup_value in its memory and looks through the 1st column of values in the table_array
until it sees a value bigger than the lookup_value, then it jumps back to the previous row and uses the data from that
                                                                row
  If the first value in the first column of the table_array is bigger than the lookup_value, then VLOOKUP returns #N/A
                             (Not Available). This is true for both exact and approximate lookups.
If the last value in the first column of the table_array is bigger than the lookup_value, then VLOOKUP returns the data
                                from the last row. This is true for only the approximate lookup.
                                   Review
                                                                      TRUE = 1
table_array   Number                        Name                      FALSE = 0
 0 Name1                               20                                         1
 5 Name2
10 Name3      FALSE = 0                     TRUE = 1
20 Name4      Exact below 1st value         Aprox. below 1st value
              Exact above last value        Aprox. above last value
              Exact Value                   Aprox. Not Exact Value
              Not Exact Value               Aprox. Exact Value
t looks in the first column, and
ow and retrieves a value from


ogical value, or a name or cell


p such as part number, part
olumns can have data that you
m earlier brackets.
e. The number 1 indicates that
 , etc.
  With TRUE or 1 (the default),
eed the lookup_value value .
 n ascending order, otherwise
 , the VLOOKUP looks for an
omitted. Instead of FALSE you




 lue. If no match it goes to step


 #N/A error is displayed. If the
 it goes to step 3
r than the lookup_value, then
m that row.
etrieves a value from this row,




n a table_array

able_array and finds an exact
atch it returns #N/A

o think about how it works:
 reater than or equal to the

mn of values in the table_array
 w and uses the data from that

 then VLOOKUP returns #N/A
 kups.
  en VLOOKUP returns the data
 up.
     Using named ranges for lookup table is usually efficient
 (highlight range, click in Name Box, type name, hit Enter). Using
Data Validation List helps the accuracy of your task (Alt + D + L is
                         keyboard shortcut).
  COLUMNS function tells you how many columns there are in a
                                range.
  VLOOKUP, Named Ranges, Exact Match = 0, COLUMNS & Data
                            Validation List
Name               Test 01 Test 02 Test 03 Test 04 Test 05
Tu Pham

Name              Test 01 Test 02 Test 03 Test 04 Test 05
Siuox RadCool           94       0       7       4      53
Dina Kim                38      48      52      71      16
Dennis Ho               67      99      60      54        7
Tu Pham                 58      43      77      41      32
Fred Tap                43      93       3      85      60
                            VLOOKUP, Approximate = omitted, 3 VLOOKUPS in one formula
Sales                             Commission Paid
                       $55,000.00

                                    Commission Table

                                                                        Commission from
Category                              Sales                 Rate        Previous Bracket
$0.00 <= Sales < $5,000.00                          $0.00       0.00%
$5,000.00 <= Sales < $10,000.00                 $5,000.00       0.50%                  0
$10,000.00 <= Sales < $20,000.00               $10,000.00       1.00%            $25.00
$20,000.00 <= Sales < $30,000.00               $20,000.00       2.00%          $125.00
$30,000.00 <= Sales < $50,000.00               $30,000.00       3.00%          $325.00
$50,000.00 <= Sales < $100,000.00              $50,000.00       6.00%          $925.00
$100,000.00 <= Sales                          $100,000.00      10.00%         $3,925.00
$55,000 check 1 $55,000 check 2
      $1,225.00                   $1,225.00
 TRIM function removes all spaces except single spaces between
                             words
               VLOOKUP (Exact Match) and TRIM

                                 The Data Dump Has Spaces
                 Data Dump                        Product ID Units
Product ID   Units Date         Comments          XVY-8409
BDU-2071      4854 5/15/2009    Comments 1        RVO-7202
LVB-2213      2073 5/16/2009    Comments 2        GZQ-9838
XRQ-2356      1713 5/17/2009    Comments 3        COV-7365
LNR-9553      7521 5/18/2009    Comments 4        QVK-9053
XVY-8409      7404 5/19/2009    Comments 5
RVO-7202      3450 5/20/2009    Comments 6
XVJ-6482      9588 5/21/2009    Comments 7
IDI-4744       837 5/22/2009    Comments 8
MWQ-7107      4846 5/23/2009    Comments 9
TRG-4292      8562 5/24/2009    Comments 10
RKQ-9102      1594 5/25/2009    Comments 11
ZLO-7423      6807 5/26/2009    Comments 12
GZQ-9838      1579 5/27/2009    Comments 13
WXF-1289      7202 5/28/2009    Comments 14
HDE-8067      5388 5/29/2009    Comments 15
BFE-9956      3489 5/30/2009    Comments 16
ESQ-9805      2394 5/31/2009    Comments 17
COV-7365      8570 6/1/2009     Comments 18
YVF-8029      3309 6/2/2009     Comments 19
IIV-2646      3326 6/3/2009     Comments 20
QVK-9053      9599 6/4/2009     Comments 21
VZZ-9501      3159 6/5/2009     Comments 22

                                   Our Data Has Spaces
                 Data Dump                        Product ID Units
Product ID   Units Date         Comments          XVY-8409
BDU-2071      4854 5/15/2009    Comments 1        RVO-7202
LVB-2213      2073 5/16/2009    Comments 2        GZQ-9838
XRQ-2356      1713 5/17/2009    Comments 3        COV-7365
LNR-9553      7521 5/18/2009    Comments 4        QVK-9053
XVY-8409      7404 5/19/2009    Comments 5
RVO-7202      3450 5/20/2009    Comments 6
XVJ-6482      9588 5/21/2009    Comments 7
IDI-4744       837 5/22/2009    Comments 8
MWQ-7107      4846 5/23/2009    Comments 9
TRG-4292      8562 5/24/2009    Comments 10
RKQ-9102      1594 5/25/2009    Comments 11
ZLO-7423      6807 5/26/2009    Comments 12
GZQ-9838      1579 5/27/2009    Comments 13
WXF-1289      7202   5/28/2009   Comments 14
HDE-8067      5388   5/29/2009   Comments 15
BFE-9956      3489   5/30/2009   Comments 16
ESQ-9805      2394   5/31/2009   Comments 17
COV-7365      8570    6/1/2009   Comments 18
YVF-8029      3309    6/2/2009   Comments 19
IIV-2646      3326    6/3/2009   Comments 20
QVK-9053      9599    6/4/2009   Comments 21
VZZ-9501      3159    6/5/2009   Comments 22

             The Data Dump Has Spaces: Use Text To Column to get rid of spaces
                 Data Dump                        Product ID Units
Product ID   Units Date       Comments            XVY-8409                 #N/A
BDU-2071      4854 5/15/2009 Comments 1           RVO-7202                 #N/A
LVB-2213      2073 5/16/2009 Comments 2           GZQ-9838                 #N/A
XRQ-2356      1713 5/17/2009 Comments 3           COV-7365                 #N/A
LNR-9553      7521 5/18/2009 Comments 4           QVK-9053                 #N/A
XVY-8409      7404 5/19/2009 Comments 5
RVO-7202      3450 5/20/2009 Comments 6
XVJ-6482      9588 5/21/2009 Comments 7
IDI-4744       837 5/22/2009 Comments 8
MWQ-7107      4846 5/23/2009 Comments 9
TRG-4292      8562 5/24/2009 Comments 10
RKQ-9102      1594 5/25/2009 Comments 11
ZLO-7423      6807 5/26/2009 Comments 12
GZQ-9838      1579 5/27/2009 Comments 13
WXF-1289      7202 5/28/2009 Comments 14
HDE-8067      5388 5/29/2009 Comments 15
BFE-9956      3489 5/30/2009 Comments 16
ESQ-9805      2394 5/31/2009 Comments 17
COV-7365      8570 6/1/2009 Comments 18
YVF-8029      3309 6/2/2009 Comments 19
IIV-2646      3326 6/3/2009 Comments 20
QVK-9053      9599 6/4/2009 Comments 21
VZZ-9501      3159 6/5/2009 Comments 22
   VLOOKUP (Exact match = 0) function and the Ampersand, &, can be used together
1)                             to create a new text item
    Hiding a column temporarily will allow you to use the "Double-click the fill handle"
2)                                         trick

                                Data Dump                                        Our System
   Type Sales            Customer Our Type                                   Type        Number
   Kite  $1,221.00       Yuliya                                              Kite         5560
   Boom $1,101.00        Yuliya                                              Boom         5590
   Kite  $1,451.00       Yuliya                                              Toy          5510
   Toy   $1,293.00       Megan                                               Other        5540
   Kite  $1,950.00       Rasik
   Toy   $1,956.00       Joel
   Other $1,163.00       Rasik
   Boom $1,177.00        Rasik
   Other $1,105.00       Igor
   Kite  $1,797.00       Megan
   Boom $1,692.00        Debbie
   Kite  $1,874.00       Wendy
   Boom $1,565.00        Olga
   Boom $1,274.00        Igor
   Boom $1,133.00        Igor
   Other $1,687.00       Olga
   Boom $1,083.00        Igor
   Kite  $1,094.00       Suix
   Boom $1,146.00        Joel
   Toy   $1,776.00       Al
   Other $1,985.00       Al
   Other $1,334.00       Debbie
   Toy   $1,602.00       Yuliya
   Kite  $1,163.00       Al
   Toy   $1,715.00       Joel
   Boom $1,155.00        Rasik
   Toy   $1,330.00       Igor
   Other $1,972.00       Debbie
   Toy   $1,728.00       Suix
   Other $1,698.00       Yuliya
   Kite  $1,759.00       Wendy
   Toy   $1,238.00       Joel
   Kite  $1,440.00       Megan
   Boom $1,990.00        Rasik
   Toy   $1,119.00       Yuliya
   Other $1,109.00       Suix
   Boom $1,808.00        Rasik
   Kite  $1,240.00       Megan
   Kite  $1,964.00       Al
   Other $1,872.00       Suix
Boom    $1,372.00   Suix
Other   $1,390.00   Al
Boom    $1,158.00   Rasik
Boom    $1,934.00   Yuliya
Boom    $1,666.00   Joel
Other   $1,647.00   Megan
Other   $1,085.00   Yuliya
Other   $1,359.00   Al
Toy     $1,293.00   Wendy
Boom    $1,249.00   Megan
Kite    $1,152.00   Yuliya
Kite    $1,024.00   Debbie
Other   $1,525.00   Suix
Other   $1,018.00   Rasik
Kite    $1,229.00   Al
Other   $1,371.00   Megan
Toy     $1,208.00   Wendy
Other   $1,785.00   Debbie
Boom    $1,832.00   Olga
Other   $1,740.00   Olga
Boom    $1,142.00   Olga
Kite    $1,499.00   Rasik
Boom    $1,778.00   Debbie
Other   $1,681.00   Igor
Boom    $1,624.00   Suix
Kite    $1,314.00   Joel
Other   $1,193.00   Joel
Other   $1,235.00   Al
Other   $1,227.00   Megan
Toy     $1,567.00   Debbie
Other   $1,694.00   Olga
Kite    $1,664.00   Al
Toy     $1,893.00   Debbie
Other   $1,128.00   Al
Toy     $1,408.00   Olga
Kite    $1,040.00   Rasik
Toy     $1,992.00   Megan
Other   $1,870.00   Joel
Toy     $1,376.00   Igor
Boom    $1,559.00   Rasik
Toy     $1,532.00   Rasik
Other   $1,876.00   Olga
Toy     $1,033.00   Rasik
Boom    $1,324.00   Wendy
Other   $1,709.00   Rasik
Kite    $1,795.00   Suix
Boom    $1,657.00   Megan
Other   $1,434.00   Olga
Boom    $1,824.00   Debbie
Toy     $1,043.00   Yuliya
Toy     $1,756.00   Yuliya
Kite    $1,023.00   Igor
Toy     $1,376.00   Suix
Other   $1,561.00   Al
Boom    $1,665.00   Rasik
Kite    $1,452.00   Igor
Toy     $1,305.00   Igor
Toy     $1,965.00   Igor
Toy     $1,919.00   Igor
Toy     $1,826.00   Igor
Other   $1,221.00   Megan
Boom    $1,193.00   Rasik
Other   $1,639.00   Suix
Toy     $1,817.00   Igor
Other   $1,288.00   Yuliya
Kite    $1,350.00   Igor
Boom    $1,609.00   Rasik
Other   $1,270.00   Yuliya
Kite    $1,143.00   Suix
Boom    $1,931.00   Rasik
Toy     $1,707.00   Debbie
Boom    $1,983.00   Debbie
Boom    $1,910.00   Debbie
Kite    $1,303.00   Joel
Other   $1,913.00   Megan
Boom    $1,972.00   Rasik
Boom    $1,253.00   Al
Kite    $1,034.00   Olga
Boom    $1,517.00   Yuliya
Kite    $1,071.00   Rasik
Kite    $1,982.00   Joel
Boom    $1,189.00   Wendy
Toy     $1,009.00   Rasik
Toy     $1,245.00   Igor
Other   $1,973.00   Megan
Toy     $1,442.00   Debbie
Toy     $1,873.00   Debbie
Kite    $1,464.00   Igor
Other   $1,159.00   Al
Boom    $1,529.00   Debbie
Toy     $1,809.00   Joel
Other   $1,832.00   Joel
Boom    $1,999.00   Suix
Other   $1,115.00   Yuliya
Toy     $1,518.00   Megan
Boom    $1,861.00   Al
Toy     $1,289.00   Yuliya
Boom    $1,390.00   Igor
Boom    $1,375.00   Megan
Other   $1,437.00   Megan
Boom    $1,333.00   Megan
Other   $1,686.00   Olga
Toy     $1,938.00   Igor
Toy     $1,685.00   Olga
Kite    $1,947.00   Suix
Kite    $1,248.00   Rasik
Other   $1,573.00   Olga
Other   $1,811.00   Olga
Boom    $1,759.00   Rasik
Kite    $1,727.00   Al
Toy     $1,276.00   Al
Other   $1,808.00   Olga
Other   $1,644.00   Yuliya
Toy     $1,872.00   Al
Kite    $1,461.00   Olga
Boom    $1,564.00   Wendy
Kite    $1,589.00   Yuliya
Other   $1,989.00   Olga
Toy     $1,194.00   Igor
Toy     $1,116.00   Rasik
Toy     $1,690.00   Debbie
Kite    $1,493.00   Yuliya
Boom    $1,680.00   Megan
Kite    $1,916.00   Olga
Kite    $1,973.00   Olga
Boom    $1,378.00   Rasik
Other   $1,182.00   Olga
Toy     $1,252.00   Yuliya
Toy     $1,101.00   Al
Boom    $1,394.00   Joel
Other   $1,926.00   Debbie
Toy     $1,379.00   Debbie
Toy     $1,528.00   Igor
Toy     $1,931.00   Megan
Boom    $1,780.00   Igor
Kite    $1,374.00   Rasik
Boom    $1,469.00   Joel
Kite    $1,066.00   Yuliya
Toy     $1,709.00   Joel
Other   $1,901.00   Debbie
                      VLOOKUP (Exact Match = 0) with Table on a different sheet or workbook

Date         Beer             Quantity Producer                        Producer
 5/16/2009   IPA                     166
 5/17/2009   Long Hammer             171
 5/18/2009   Good Beer                42
 5/19/2009   Good Beer                78
 5/20/2009   Good Beer                99
 5/21/2009   Pale Ale                198
 5/22/2009   Long Hammer             124
 5/23/2009   Long Hammer              69
 5/24/2009   Good Beer                67
 5/25/2009   Pale Ale                175
 5/26/2009   IPA                      52
 5/27/2009   Long Hammer              21
 5/28/2009   Long Hammer              77
 5/29/2009   Good Beer                58
 5/30/2009   IPA                       6
 5/31/2009   IPA                     174
  6/1/2009   Good Beer                94
  6/2/2009   Pale Ale                 77
  6/3/2009   Long Hammer             109
  6/4/2009   Long Hammer              11
  6/5/2009   IPA                     198
  6/6/2009   Pale Ale                 46
  6/7/2009   Pale Ale                167
  6/8/2009   Good Beer                68
  6/9/2009   Pale Ale                 49
 6/10/2009   Long Hammer             140
 6/11/2009   IPA                      19
 6/12/2009   Long Hammer              24
 6/13/2009   Pale Ale                198
 6/14/2009   Long Hammer              71
 6/15/2009   Pale Ale                 60
 6/16/2009   Pale Ale                186
 6/17/2009   IPA                      33
 6/18/2009   Long Hammer              42
 6/19/2009   IPA                      22
 6/20/2009   Good Beer               198
 6/21/2009   Good Beer               171
 6/22/2009   Pale Ale                 51
 6/23/2009   Good Beer                 4
 6/24/2009   Good Beer                24
 6/25/2009   IPA                     169
 6/26/2009   Good Beer               163
k
Beer          Producer
Good Beer     Great Beer Inc.
Long Hammer   Red Hook
IPA           Pyramid Brewery
Pale Ale      Sierra Nevada Brewing Co.
              For more on this topic (MID and VLOOKUP), see these videos:
  Adding 0 (zero) to a number that is a text string will convert it back to a number (MID
                          automatically delivers a text string)

                            VLOOKUP and MID
Item ID    Area                       Code                    Area
NICF-55-AM                                               50   Front
OCNQ-59-TP                                               51   Bins
KDTY-58-TQ                                               52   Back Bins
UGPW-52-WS                                               53   Shelf
XHOL-59-VD                                               54   Storage Room
                                                         55   Freezer
                                                         56   Yard
                                                         57   Truck Port
                                                         58   Office
                                                         59   Basement
Excel Magic Trick 318: MID Text (substring) VLOOKUP Trouble

Excel Magic Trick 317: MID function: Pattern Recognition
                                         Astrix * means 1 or more characters
                                        Ampersand (Shift + 7) is the join symbol


                   Partial Text VLOOKUP (Exact Match) with wildcards
Producer                      Beer
Great Beer Inc.               Good Beer
Red Hook                      Long Hammer
Pyramid Brewery               IPA
Sierra Nevada Brewing Co.     Pale Ale

Partial Text                 Producer
Great Beer
Red Hook
Pyramid
Sierra Nevada
                  For more about Currency Rate Web Querry see this video:
                 For more about LEFT, LEN and FIND functions, see this video:

                   VLOOKUP Into Currency Rate Web Query
Currency                           Dollars           Japanese Yen
Japanese Yen to US Dollar                    $500.00
Excel Magic Trick #108: Exchange Rate table
Highline Excel Class 13: Text functions & AMPERSAND
  Be Careful of Formatting when you use VLOOKUP
Grade                    Percentage
Table     Names          Grade       Letter Grade   Scores Scores Scores Scores
0.55 F    Sioux Lim           82.25%                     52     89     90     98
0.65 D    Fred Snappy         69.50%                     73     83     57     65
0.75 C    Luong Radcoolinator84.50%                      81     80     95     82
0.85 B    Mo Dan              68.25%                     67     62     69     75
0.95 A
          Sioux Lim           82.25% C
Grade
Table     Fred Snappy         69.50% D
0.95 A    Luong Radcoolinator84.50% C
0.85 B    Mo Dan              68.25% D
0.75 C
0.65 D
0.55 F
Max
      400

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:9/15/2012
language:Unknown
pages:23