|
|
Excel Chapter 19B of 24: LOOKUP Function In ExcelThe 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: - 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 also works with references (addresses) which is interesting when you work with dates as condition: 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 also works with references (addresses) which is interesting when you work with dates as condition: |
Next Chapter: VLOOKUP Function in Excel |
|
|
LOOKUP Function In Excel