Excel Chapter 19B of 24: LOOKUP  Function In Excel

The LOOKUP function allows to look for a value in a column 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 that you are submitting as second argument.

LOOKUP is used in two situations:
-On an invoice or other documents you enter the name of a client 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. Unfortunately 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 accounts...)

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.

Next Chapter: VLOOKUP Function in Excel