Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

VLOOKUP function without formulas by e51r8Xi

VIEWS: 6 PAGES: 23

									                                                      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
e it looks in the first column,
hat row and retrieves a value
 ll.

gical value, or a name or cell


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




ue. 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

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

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

n 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 Previous
Category                              Sales                 Rate        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
1)                         together 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   Pale Ale                123
 5/17/2009   IPA                     170
 5/18/2009   Pale Ale                119
 5/19/2009   Pale Ale                184
 5/20/2009   IPA                      88
 5/21/2009   Pale Ale                 75
 5/22/2009   IPA                      34
 5/23/2009   IPA                      33
 5/24/2009   Long Hammer              61
 5/25/2009   IPA                     103
 5/26/2009   Long Hammer             136
 5/27/2009   Long Hammer               0
 5/28/2009   Good Beer                35
 5/29/2009   Good Beer                 9
 5/30/2009   Pale Ale                 93
 5/31/2009   Long Hammer             121
  6/1/2009   Long Hammer             198
  6/2/2009   IPA                      57
  6/3/2009   Pale Ale                177
  6/4/2009   Good Beer                36
  6/5/2009   Good Beer                34
  6/6/2009   Good Beer                16
  6/7/2009   Pale Ale                130
  6/8/2009   Good Beer               170
  6/9/2009   Good Beer                40
 6/10/2009   Pale Ale                 48
 6/11/2009   Good Beer               143
 6/12/2009   Long Hammer             125
 6/13/2009   Pale Ale                160
 6/14/2009   Good Beer               162
 6/15/2009   IPA                     174
 6/16/2009   Good Beer                50
 6/17/2009   Good Beer                48
 6/18/2009   Good Beer               111
 6/19/2009   IPA                      89
 6/20/2009   Good Beer                98
 6/21/2009   Pale Ale                 60
 6/22/2009   Pale Ale                158
 6/23/2009   Good Beer                59
 6/24/2009   Good Beer                53
 6/25/2009   Pale Ale                180
 6/26/2009   Good Beer               146
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

								
To top