Income is Greater Than But Less Than or

Tax Rate
or Equal To… Equal To…
Enter Income: $32,650 $0 $2,650 15.00%
The Tax Rate is: 31.00% $2,651 $27,300 28.00%
$27,301 $58,500 31.00%
$58,501 $131,800 36.00%
$131,801 $284,700 39.60%
$284,701 45.25%

Looks up the value in the first column (here D) of the lookup table
Returns the corresponding value in a specified table column (here 3--> F)
The Table Array is the range that contains the lookup table
[Range Lookup] is an optional logical value that specifies whether you want VLOOKUP to find an approximate or an exact mat

If 1 / TRUE or omitted, an approximate match is returned.

If 0 / FALSE, an exact match.

Note that an exact match is not required. If an exact match is not found in the fi rst column
of the lookup table, the VLOOKUP function uses the next largest value that is less than the
lookup value. In other words, the function uses the row in which the value you want to
look up is greater than or equal to the row value but less than the value in the next row. In
the case of a tax table, this is exactly what you want to happen.

Many tables are arranged so that the key piece of data, the data that makes a certain row
unique, is in the far-left column. While Excel has many lookup functions, VLOOKUP was
designed for just that situation.
 There are four pieces of information that you will need in order to build the VLOOKUP syntax:

The value you want to look up, also called the lookup value.
approximate or an exact match:
The range where the lookup value is located. Remember that the lookup value should always

The column number in the range that contains the return value. For example, if you specify B

Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an e

Now put all of the above together as follows:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range
 r to build the VLOOKUP syntax:

he lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in ce

e. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.

match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or appro

e column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
le, if your lookup value is in cell C2 then your range should start with C.

e will always be TRUE or approximate match.

 Last Name First Name Department Extension
Enter a name --> Allen Yolanda Sales 4466

Last Name First Name Department Extension

Allen Yolanda Sales 4466
Baker Nancy Operations 3432
Bunnel Ken Marketing 4422
Charles Larry Administration 2822
Cramden Moe Administration 1231
Davis Rita Administration 2604
Dunwell James Operations 3983
Ellis Pamela Data Processing 2144
Endow Ed Data Processing 1102
Date Hired
35859

Date Hired
3/5/1998
4/16/2003
12/1/2010
9/16/1999
3/12/2011
4/15/2009
2/9/2012
3/24/2004
11/12/2003
 Income is Greater Than
$0 $2,651 $27,301 $58,501
or Equal To…

Enter Income: $21,566 But Less Than… $2,650 $27,300 $58,500 $131,800

The Tax Rate is: 28.00% Tax Rate 15.00% 28.00% 31.00% 36.00%

Note:

The HLOOKUP function works just like the VLOOKUP function except that the lookup
table is arranged horizontally instead of vertically. The HLOOKUP function looks up the
value in the first row of the lookup table and returns the corresponding value in a specified table row.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The HLOOKUP function’s arguments are as follows:

■ lookup_value: The value to be looked up in the fi rst row of the lookup table.
■ table_array: The range that contains the lookup table.
■ row_index_num: The row number within the table from which the matching value
is returned.
■ range_lookup: Optional. If TRUE or omitted, an approximate match is returned.
(If an exact match is not found, the next largest value less than lookup_value
is returned.) If FALSE, HLOOKUP will search for an exact match. If HLOOKUP can’t
find an exact match, the function returns #N/A.
$131,801 $284,701

$284,700

39.60% 45.25%
 Income is Greater Than
But Less Than… Tax Rate
or Equal To…
Enter Income: $123,409 $0 $2,650 15.00%
The Tax Rate is: 36.00% $2,651 $27,300 28.00%
$27,301 $58,500 31.00%
$58,501 $131,800 36.00%
$131,801 $284,700 39.60%
$284,701 45.25%

The LOOKUP function looks in a one-row or one-column range (lookup_vector) for a

value (lookup_value) and returns a value from the same position in a second one-row or
one-column range (result_vector).

The LOOKUP function has the following syntax:

LOOKUP(lookup_value,lookup_vector,result_vector)
The function’s arguments are as follows:
■ lookup_value: The value to be looked up in the lookup_vector.
■ lookup_vector: A single-column or single-row range that contains the values to
be looked up. These values must be in ascending order.
■ result_vector: The single-column or single-row range that contains the values
to be returned. It must be the same size as the lookup_vector.

Caution:

Values in the lookup_vector must be in ascending order. If the values in the fi rst column are not arranged in
ascending order, the LOOKUP function may return an incorrect value.

Note that LOOKUP (as opposed to VLOOKUP) requires two range references: a range to be
looked in and a range that contains result values. VLOOKUP, on the other hand, uses a
single range for the lookup table, and the third argument determines which column to use
for the result. This argument, of course, can consist of a cell reference.
 1st Nails
2nd Screws
3rd Nuts
Finished Bolts

=CHOOSE(2,A2,A3,A4,A5) 2nd
=CHOOSE(4,B2,B3,B4,B5) Bolts
=CHOOSE(3,"Wide",115,"world",8) world
CHOOSE function

Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index numbe
For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used a

Syntax
CHOOSE(index_num, value1, [value2], ...)

The CHOOSE function syntax has the following arguments:

Index_num Required. Specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or referen

If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.

If index_num is a fraction, it is truncated to the lowest integer before being used.

Value1, value2, ... Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or a
The arguments can be numbers, cell references, defined names, formulas, functions, or text.
Survey Results
Resp. Q1 Q2 Q3 Q4 Q5 Avg. Result
R01 5 5 5 5 4 4.8 Agree
R02 4 4 4 4 4 4 Agree
R03 1 2 2 2 2 1.8 Strongly Disagree
R04 5 4 4 3 4 4 Agree
R05 3 3 2 2 2 2.4 Disagree
R06 1 1 1 1 1 1 Strongly Disagree
R07 5 4 4 4 5 4.4 Agree
R08 1 1 1 1 1 1 Strongly Disagree
R09 5 5 5 5 5 5 Strongly Agree
R10 4 5 5 5 5 4.8 Agree
R11 5 4 4 4 4 4.2 Agree
R12 2 3 4 4 4 3.4 Neutral
R13 5 5 5 5 5 5 Strongly Agree
R14 1 1 1 1 1 1 Strongly Disagree
R15 3 3 3 3 3 3 Neutral
R16 5 5 5 5 5 5 Strongly Agree
R17 4 4 4 5 5 4.4 Agree
R18 4 4 4 5 5 4.4 Agree
R19 4 4 5 4 5 4.4 Agree
R20 2 2 3 3 2 2.4 Disagree
R21 4 4 4 4 5 4.2 Agree
R22 5 5 4 4 4 4.4 Agree
R23 2 1 0 1 2 1.2 Strongly Disagree
R24 4 4 3 3 4 3.6 Neutral
R25 5 5 5 4 5 4.8 Agree
R26 5 5 5 5 5 5 Strongly Agree
R27 1 1 2 2 2 1.6 Strongly Disagree
R28 1 1 1 1 2 1.2 Strongly Disagree
R29 4 4 3 3 2 3.2 Neutral
R30 3 3 2 2 2 2.4 Disagree
R31 3 3 3 4 3 3.2 Neutral
R32 5 5 5 5 4 4.8 Agree
R33 3 4 4 4 4 3.8 Neutral
R34 4 4 4 4 4 4 Agree
R35 1 1 1 1 1 1 Strongly Disagree
R36 1 1 2 1 1 1.2 Strongly Disagree
R37 3 4 3 3 3 3.2 Neutral
R38 3 2 1 1 1 1.6 Strongly Disagree
R39 5 5 5 4 4 4.6 Agree
R40 3 3 3 4 3 3.2 Neutral
R41 3 3 4 4 5 3.8 Neutral
R42 2 3 2 2 3 2.4 Disagree
R43 5 4 5 5 5 4.8 Agree
R44 4 4 4 4 4 4 Agree
R45 5 4 4 4 5 4.4 Agree
R46 3 3 3 3 3 3 Neutral
R47 3 2 2 2 2 2.2 Disagree
R48 4 5 5 5 5 4.8 Agree
R49 5 5 5 5 5 5 Strongly Agree
R50 5 4 4 3 2 3.6 Neutral
R51 2 1 1 2 2 1.6 Strongly Disagree
R52 2 1 1 2 3 1.8 Strongly Disagree
R53 5 5 5 5 5 5 Strongly Agree
R54 2 1 1 1 1 1.2 Strongly Disagree
R55 4 4 4 3 3 3.6 Neutral
R56 4 4 4 5 5 4.4 Agree
R57 5 5 4 3 4 4.2 Agree
R58 2 1 1 1 2 1.4 Strongly Disagree
R59 2 1 0 0 0 0.6 Err:502
R60 2 3 3 4 5 3.4 Neutral
R61 5 5 5 5 5 5 Strongly Agree
R62 2 3 3 3 3 2.8 Disagree
R63 4 4 4 3 4 3.8 Neutral
R64 1 1 1 1 1 1 Strongly Disagree
R65 4 4 3 3 3 3.4 Neutral
R66 2 2 2 1 1 1.6 Strongly Disagree
R67 1 1 1 1 1 1 Strongly Disagree
R68 3 2 3 3 3 2.8 Disagree
R69 3 2 2 3 3 2.6 Disagree
R70 1 1 1 1 2 1.2 Strongly Disagree
R71 5 5 5 4 3 4.4 Agree
R72 1 1 1 1 1 1 Strongly Disagree
R73 1 1 1 1 1 1 Strongly Disagree
R74 3 4 5 5 5 4.4 Agree
R75 3 3 3 4 4 3.4 Neutral
R76 4 4 4 4 3 3.8 Neutral
R77 5 5 5 5 5 5 Strongly Agree
R78 3 3 4 5 5 4 Agree
R79 2 2 2 2 2 2 Disagree
R80 3 2 1 1 1 1.6 Strongly Disagree
R81 5 5 4 4 4 4.4 Agree
R82 3 3 2 2 2 2.4 Disagree
R83 5 5 5 5 5 5 Strongly Agree
R84 5 5 5 5 4 4.8 Agree
R85 4 4 5 5 5 4.6 Agree
R86 1 1 1 1 1 1 Strongly Disagree
R87 1 1 1 1 1 1 Strongly Disagree
R88 4 4 5 5 5 4.6 Agree
R89 1 1 1 2 2 1.4 Strongly Disagree
R90 3 3 3 3 3 3 Neutral
R91 3 3 3 2 3 2.8 Disagree
R92 5 5 5 4 4 4.6 Agree
R93 3 3 2 2 2 2.4 Disagree
R94 5 5 5 5 5 5 Strongly Agree
R95 1 1 1 1 1 1 Strongly Disagree
R96 2 2 3 4 4 3 Neutral
R97 4 4 4 4 4 4 Agree
R98 1 1 1 1 1 1 Strongly Disagree
R99 1 2 2 2 3 2 Disagree
R100 4 3 4 3 3 3.4 Neutral
R101 2 2 2 3 3 2.4 Disagree
R102 4 4 4 4 3 3.8 Neutral
R103 1 1 0 1 2 1 Strongly Disagree
R104 4 4 3 3 3 3.4 Neutral
R105 4 4 4 4 3 3.8 Neutral
R106 4 4 4 5 4 4.2 Agree
R107 1 1 1 2 1 1.2 Strongly Disagree
R108 1 1 0 1 2 1 Strongly Disagree
The example shows survey data from a set of respondents
Column B:F indicate responses on five measures of a specific service
Column G calculates an average that ranges from 1 to 5
Task: To add words to colum H to characterise the overall rating from the respondent
Formula used in cell H4:
=CHOOSE(G4,"Strongly Disagree","Disagree","Neutral","Agree","Strongly Agree")
Student Score Grade Grades
CECILIA ALBERT 96 A 0F
KRISTEN FOREMAN 88 B 65 D
DOROTHY MARTINEZ 76 C 70 C
LUIS FISCHER 93 A 85 B
TERRY SKINNER 89 B 92 A
SHELLEY OSBORN 69 D
MARVIN SCHULTZ 77 C
JOE TRUJILLO 93 A
WILLIAM SKINNER 92 A
CAROLYN CURRY 68 D
CARL ZAMORA 96 A
LUIS CALLAHAN 85 B
JENNY PHILLIPS 73 C
LUCY WASHINGTON 76 C
KAYLA CLARKE 69 D
NELLIE GRAHAM 93 A
VICTOR LANGLEY 74 C
MYRTLE MANN 88 B
MICHAEL COTE 70 C
CAROL HEAD 71 C
ANTHONY MYERS 93 A
BILLY ORR 74 C
The table is built showing the scores where the grading scale changes from one grade to next
The lookup table here needs to be sorted in ascending sequence
0 - 65 --> F
> 65 & < = 70 ---> D
The numeric value of the lookup table must be on the left most column in the lookup table
The third argument specifies the column of the lookup table to be returned
Final argument can be TRUE or omitted
Rep Date Sale Amt Rep Name Rep No.
R5 2/17/2006 181.85 Manny R4
R7 2/17/2006 144.78 Michael R8
R5 2/17/2006 139.13 Manny R6
R3 2/17/2006 155.54 Marc R5
R6 2/18/2006 178.49 Linda R3
R3 2/18/2006 158.21 Marc R7
R8 2/18/2006 158.79 Jerry
R8 2/18/2006 167.6 Jerry
R2 2/19/2006 155.94 #N/A
R3 2/19/2006 101.56 Marc
R4 2/19/2006 163.24 Amar
R3 2/19/2006 177.56 Marc
R6 2/20/2006 155.13 Linda
R4 2/20/2006 161.35 Amar
R4 2/20/2006 147.47 Amar
R3 2/20/2006 174.8 Marc
R7 2/21/2006 174.48 Michael
R6 2/21/2006 195.48 Linda
R5 2/21/2006 123.71 Manny
R8 2/21/2006 161.46 Jerry
R4 2/22/2006 145.62 Amar
R6 2/22/2006 178.17 Linda
R5 2/22/2006 138.67 Manny
R4 2/22/2006 146.97 Amar
R7 2/23/2006 198.95 Michael
R6 2/23/2006 138.55 Linda
R5 2/23/2006 167.43 Manny
R5 2/23/2006 144.65 Manny
R5 2/24/2006 114.16 Manny
R7 2/24/2006 165.31 Michael
R4 2/24/2006 120.93 Amar
R8 2/24/2006 155.12 Jerry
R8 2/25/2006 100.47 Jerry
R5 2/25/2006 149.58 Manny
R8 2/25/2006 171.48 Jerry
R4 2/25/2006 130.36 Amar
R4 2/26/2006 171.03 Amar
R6 2/26/2006 173.68 Linda
R5 2/26/2006 143.69 Manny
R6 2/26/2006 190.95 Linda
R8 2/27/2006 180.91 Jerry
R8 2/27/2006 131.38 Jerry
R4 2/27/2006 153.56 Amar
R4 2/27/2006 110.92 Amar
R7 2/28/2006 122.14 Michael
R5 2/28/2006 176.6 Manny
R5 2/28/2006 167.55 Manny
R3 2/28/2006 123.82 Marc
R3 3/1/2006 168.09 Marc
R4 3/1/2006 162.45 Amar
R5 3/1/2006 159.43 Manny
R6 3/1/2006 120.83 Linda
R3 3/2/2006 112.66 Marc
R3 3/2/2006 104.26 Marc
R4 3/2/2006 108.86 Amar
R7 3/2/2006 182.16 Michael
R4 3/3/2006 117.15 Amar
R7 3/3/2006 104.32 Michael
R6 3/3/2006 115.8 Linda
R5 3/3/2006 175.86 Manny
R4 3/4/2006 115.24 Amar
R7 3/4/2006 191.87 Michael
R7 3/4/2006 114.89 Michael
R8 3/4/2006 117.78 Jerry
Rep Name
Amar In this exercise, Excel finds the exact match in the Lookup table
Jerry
Linda The lookup table has rep numbers and names
Manny This table do not need to be sorted by the rep number field
Marc FALSE is the fourth parameter: close mahces are not acceptable in this case
Michael Since VLOOKUP is very time-intensive, the live formulas can be converted to their c
in the Lookup table

p number field
s are not acceptable in this case
ive formulas can be converted to their current values
RSVP's LAST WEEK RSVP's THIS WEEK
There?
VERONICA HAHN ARTHUR FLETCHER ARTHUR FLETCHER
ELLEN LINDSAY BARBARA BERGER BARBARA BERGER
CECILIA HARMON CANDICE GLENN CANDICE GLENN
DONALD TYLER CECILIA HARMON CECILIA HARMON
NICOLE KELLY CHRIS PAGE CHRIS PAGE
MARCIA ERICKSON CHRISTOPHER DONOVAN #N/A
BARBARA BERGER JOANN BROOKS #N/A
CHRIS PAGE ELLEN LINDSAY ELLEN LINDSAY
CANDICE GLENN JACOB MCINTYRE #N/A
STACY DUNLAP JOHN GARRISON JOHN GARRISON
ARTHUR FLETCHER KATHLEEN RICHARD KATHLEEN RICHARD
KATHLEEN RICHARD MARCIA ERICKSON MARCIA ERICKSON
JOHN GARRISON MYRTLE MOON #N/A
NICOLE KELLY NICOLE KELLY
STACY DUNLAP STACY DUNLAP
VERONICA HAHN VERONICA HAHN
Note

The worksheet has two lists

Column A shows lasts week's version of who was coming to an event
Column C shows this week's version of who is coming to an egent
Task is to find out if there is anyone who is new in this list

Here the only choice to return is Column 1, which is the third argument
Who dropped from the last week's list?
VERONICA HAHN
ELLEN LINDSAY
CECILIA HARMON
#N/A
NICOLE KELLY
MARCIA ERICKSON
BARBARA BERGER
CHRIS PAGE
CANDICE GLENN
STACY DUNLAP
ARTHUR FLETCHER
KATHLEEN RICHARD
JOHN GARRISON
 ShipmentID Destination
SI3049224 51102

ShipmentID CustomerID Date OriginationPostalCode DestinationPostalCode

SI3049210 CI384471 5/20/2007 59686 77408
SI3049211 CI495231 5/20/2007 24348 91936
SI3049212 CI429120 5/20/2007 70216 83501
SI3049213 CI418125 5/20/2007 84196 21660
SI3049214 CI782990 5/20/2007 13193 92518
SI3049215 CI102300 5/20/2007 27910 76842
SI3049216 CI560742 5/20/2007 73820 21393
SI3049217 CI483289 5/20/2007 34245 33975
SI3049218 CI762179 5/20/2007 87569 11471
SI3049219 CI278943 5/20/2007 28371 72853
SI3049220 CI213987 5/20/2007 18024 31069
SI3049221 CI907745 5/20/2007 70812 53604
SI3049222 CI299868 5/20/2007 33242 23892
SI3049223 CI503324 5/20/2007 58997 37121
SI3049224 CI512191 5/20/2007 14020 51102
SI3049225 CI932656 5/20/2007 56345 28404
SI3049226 CI514577 5/20/2007 34262 99198
SI3049227 CI803799 5/20/2007 92043 65330
SI3049228 CI337976 5/20/2007 35991 48458
SI3049229 CI503213 5/20/2007 92048 91421
SI3049230 CI611776 5/21/2007 29605 33396
SI3049231 CI829632 5/21/2007 33560 58962
SI3049232 CI991251 5/21/2007 45388 81656
SI3049233 CI347067 5/21/2007 78871 81265
SI3049234 CI790369 5/21/2007 90578 36504
SI3049235 CI690583 5/21/2007 69376 34806
SI3049236 CI521520 5/21/2007 44836 92132
SI3049237 CI436088 5/21/2007 31752 24117
SI3049238 CI885753 5/21/2007 65052 94728
SI3049239 CI664043 5/21/2007 89877 96043
SI3049240 CI864729 5/21/2007 46994 56090
SI3049241 CI208655 5/21/2007 28282 85072
SI3049242 CI495212 5/21/2007 41353 58504
SI3049243 CI177763 5/21/2007 88371 63309
SI3049244 CI239227 5/21/2007 44493 66526
Instructions 1 In cell C3, enter the formula =VLOOKUP(B3, Shipments, 5, FALSE). Cell B3
the formula uses to look up values in the Shipments table, is blank, so the #
code appears in cell C3.

2 In cell B3, enter SI3049224, and press Enter. The value 51102 appears in c

3 In cell C3, edit the formula so that it reads =VLOOKUP(B3, Shipments, 2, F

The formula now finds its target value in table column 2 (the CustomerID c
the value CI512191 appears in cell C3.

4 In cell C3, edit the formula so that it reads =VLOOKUP(B3, Shipments, 4, T

Changing the last argument to TRUE enables the VLOOKUP formula to find
match for the ShipmentID in cell B3, whereas changing the column to 4
means the formula gets its result from the OriginationPostalCode column. T
14020 appears in cell C3.

5 In cell B3, enter SI3049209. The value in cell B3 is smaller than the smalle
the Shipments table’s first column, so the VLOOKUP formula displays the #
code in cell C3.

6 In cell B3, enter SI3049245. The ShipmentID entered into cell B3 is greate
last value in the table’s first column, so the VLOOKUP formula displays the
in the target column (in this case, the fourth column). Therefore, the incorr
44493 appears in cell C3. The error occurs because the range_lookup argum
to TRUE.

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-

Use VLOOKUP, one of the lookup and reference functions, when you need

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the

Tip: The secret to VLOOKUP is to organize your data so that the value you
3, Shipments, 5, FALSE). Cell B3, which
ipments table, is blank, so the #N/A error

er. The value 51102 appears in cell C3.

=VLOOKUP(B3, Shipments, 2, FALSE).

ble column 2 (the CustomerID column), so

=VLOOKUP(B3, Shipments, 4, TRUE).

s the VLOOKUP formula to find an approximate
as changing the column to 4
OriginationPostalCode column. The value

ell B3 is smaller than the smallest value in

LOOKUP formula displays the #N/A error

ID entered into cell B3 is greater than the

VLOOKUP formula displays the last value
h column). Therefore, the incorrect value
ecause the range_lookup argument is set

ookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

ence functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part n

e where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match –

your data so that the value you look up (part number) is to the left of the return value you want to find (price of the part).
motive part by the part number.

or Approximate Match – indicated as 0/FALSE or 1/TRUE).