# VLOOKUP function without formulas by e51r8Xi

VIEWS: 6 PAGES: 23

• pg 1
```									                                                      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

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

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

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