**The LOOKUP
function in Excel**
The LOOKUP
function allows to look for a value in a colunm or a row based on a
certain value in another column or row. So you look up for "John" in
column A and you get his address in column B or you look up for "John"
in row 1 and you get his address in row 2.
Basically
LOOKUP does what both VLOOKUP and HLOOKUP do. But unlike with VLOOKUP and
HLOOKUP you cannot specify if you are looking for an exact match or not.
LOOKUP is always looking up for an exact match.
LOOKUP is
always looking up in the first column or row of the array thet you are
submitting as second argument.
LOOKUP is
used in two situations: -On an invoice or other documents you enter the
name of a clientt and in the next 2 cells an Excel LOOKUP formula brings
in the address 1 and address 2. Less manual entries less mistakes. On the
same invoice you type in the product number and, its description, unit and
unit price are carried over by Excel LOOKUP's.
- You have a
set of sales data and you want to subtotal sales by cities.
Unfortunatally the city in which the client lives in not part of the set
of data. This information exists in another set of data that you import on
sheet 2. With Excel LOOKUP you can bring the name of the city within your
set of data on sales and calculate the subtotal by
cities.
**IMPORTANT NOTE**: The values in the set of
cells that you are searching within MUST be in ascending order
(1,2,3,4...) or in alphabetical order.
LOOKUP
Working vertically with
columns
**=LOOKUP(5,A1:A6,B1:B6)** looks up for 5 in A1:A6, and returns the value from B1:B6 that's in
the same row as the 5. If 5 is not found **LOOKUP** will use the next
lower value (4 or 3...). You cannot specify that you want 5 and nothing
else. **LOOKUP** will also use the next value down alphabetically when
you work with strings.
LOOKUP also works with strings (text) but you
have to use double quotes:
**=LOOKUP("John",A1:A6,B1:B6)**
LOOKUP also works with references (addresses)
which is interesting when you work with dates as
condition:
**=LOOKUP(J20,A1:A6,B1:B6)**
means look up for the date in J20 in cells A1 to A6 and return the value
found in cells B1 to B6.
**A TIP**: Why not use A100 and
B100 instead of A6 and B6 so that you can add information in your table
(new clients, new acounts...)
Working with
horizontally with rows
**=LOOKUP(5,A1:H1,A5:H5)** looks up for 5 in A1:H1, and returns the value from A5:H5 that's in
the same column as the 5. If 5 is not found **LOOKUP** will use the
next lower value (4 or 3...). You cannot specify that you want 5 and
nothing else. **LOOKUP** will also use the next value down
alphabetically when you work with strings.
LOOKUP also works with strings (text) but you
have to use double quotes:
**=LOOKUP("John",A1:H1,A5:H5)**
LOOKUP also works with references (addresses)
which is interesting when you work with dates as
condition:
**=LOOKUP(J20,A1:H1,A5:H5)**
means look up for the date in J20 in cells A1 to H1 and return the
value found in cells A5 to
H5. |