Excel Lookup Functions: Mastering Data Searches With Ease

When working with Excel, I often come across situations where I need to search for specific data in a table or a list. Luckily, Excel has a plethora of functions to make this task easier. Whether it’s retrieving a price from a specific range or finding the corresponding value for a given criteria, Excel’s lookup functions are incredibly handy. From VLOOKUP and HLOOKUP to the newer XLOOKUP and XMATCH, each of these functions allows you to search and extract data in different ways. For instance, VLOOKUP is great for vertical lookups, where I need to find data in the same row but a different column, while HLOOKUP works the other way around for horizontal lookups.

Using these functions in Excel is about understanding the arguments each one takes, like the lookup value, the range to search in, and the column to return the value from. It’s important to get the syntax right—like making sure the data is in ascending order for certain functions, or correctly using true or false arguments to return exact matches or allow approximate matches. And for cases where I need to be case-sensitive or work with arrays, there are specific ways to format the formula. Getting comfortable with these functions means I can handle a variety of tasks much faster, like sorting, filtering, and using formulas to locate and manipulate data effectively.

1. ADDRESS Function

Syntax

ADDRESS(row_num, column_num, [abs_num], [A1], [sheet_text])

Arguments

• row_num: The row number.
• column_num: The column number.
• [abs_num]: Specifies the type of reference – absolute or relative (optional).
• [A1]: Determines A1 or R1C1 style for the cell reference (optional).
• [sheet_text]: The sheet name to include in the reference (optional).

Notes

• By default, I get an absolute cell reference when I don’t specify an abs_num.

Example

When I use ADDRESS, I can craft a cell’s address in different ways:

For R1C1 reference style:

• I can get a relative reference for both row and column.
• Or mix it up with relative row and absolute column, or vice versa.
• Of course, I can stick with an absolute reference, too.

Switching to A1 reference style lets me do the same:

• Craft a relative reference.
• Combine relative and absolute for row and column as needed.
• Go full absolute for the cell reference.

2. AREAS Function

Syntax

AREAS(reference)

Arguments

• reference: A cell or range of cells I want to count.

Notes

• I can input either a single cell, a range, or a named range for the reference.
• If I’m referencing multiple areas, I need to put them in extra parentheses and separate them with commas.

Example

I tried the AREAS function with a named range and here’s what happened:

The range had three columns, and correctly, it gave me the number 3.

Here’s another view:

3. CHOOSE Function

Syntax

My use of the CHOOSE function starts with its syntax, which is pretty straightforward:

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

Arguments

When I’m explaining the function to someone, I detail each component:

• index_num: This is me picking out the number that corresponds to the desired item’s position in my list.
• value1, [value2], …: These are the options I provide from which CHOOSE will retrieve the data. I can pick a cell or type a value directly.

Notes

For flexibility, I have the option to reference cells directly or punch in the values I need straight into the formula. It’s super handy when I’ve got static data or when cell references would make things too complex.

Example

Let me show you a scenario where I’m using CHOOSE to switch between various statistics like sum, average, max, and min. Take a look at this formula:

=CHOOSE(VLOOKUP(K2, Q1:R4, 2, FALSE), SUM(O2:O9), AVERAGE(O2:O9), MAX(O2:O9), MIN(O2:O9))

There’s a mini-table I’ve set up that serves as a navigation panel, labeled with the operations I need, alongside their corresponding serial numbers. Also, I’ve got myself a drop-down list to toggle between these calculations easily. Then, VLOOKUP fetches the index number from my little table based on the selection from the dropdown.

I don’t just slot in values; I use formulas tailored for each statistic within the CHOOSE function—neat, right?

4. COLUMN Function

Syntax

I use COLUMN([reference]) when I need to know the number of a column.

Arguments

• reference: It’s all about the cell I’m interested in. I pop that in to see what its column number is.

Notes

• Just to keep in mind: throwing an array or multiple cells at it? Expect an array back, or just the number from the leftmost cell’s column.
• Leave it blank, and it tells me where I am—handy for the current cell column number.

Example

If I’m curious about cell A1’s column number, I just go =COLUMN(A1). Doing this, I know A1 is in column 1. Pretty neat for matching things up, right?

And here’s something cool—I can auto-generate serial numbers in headers using =COLUMN(). It defaults to my current location, so I just drag the formula across, and it counts up for me!

5. COLUMNS Function

Syntax

COLUMNS(array)

Arguments

• array: The range or array of cells I’m keen to count columns in.

Notes

• Named ranges? No sweat, they work just as well.
• Just remember, COLUMNS is all about the count, not what’s inside the cells.

Example

Check this out—I’ve got a range from A1 to F1, and I want to tally up the columns. Here’s how I do it:

6. FORMULATEXT Function

Syntax

FORMULATEXT(reference)

Arguments

• reference: The cell reference to retrieve the formula in text format.

Notes

• The workbook you’re referencing must be open, or the function won’t retrieve the formula.
• References to multiple cells will output the formula from the upper-left cell only.
• An error #N/A is shown if the referenced cell:
  • doesn’t contain a formula,
  • has a formula exceeding 8192 characters,
  • is in a protected cell, or
  • is in a closed external workbook.
• Circular references will produce results from both cells involved.

Example

I played around with the function and found out some things: get a cell with no formula and you’re looking at a #N/A error. The image below shows different scenarios of using FORMULATEXT. It’s clear that knowing the function that’s driving the cell’s value can be a handy thing.

7. HLOOKUP Function

Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments

• lookup_value: The value I’m searching for in the first row.
• table_array: The array or range containing the data to search.
• row_index_num: Indicates which row from the top of table_array to return a value from.
• [range_lookup]: Optional. TRUE by default. If FALSE, searches for an exact match to lookup_value.

Notes

• I can use wildcards like * or ? for partial matches when range_lookup is TRUE.
• Exact match (FALSE) doesn’t require the data to be sorted, but approximate match (TRUE) does, and should be in ascending order.
• Non-exact match (TRUE) finds the closest value that is less than or equal to the lookup_value when an exact match isn’t found in the row.

Example

Let’s say I want to quickly retrieve some data based on a drop-down list, say, a zone name, without repeatedly modifying the formula. I place the zone name in cell C7 and use it as my lookup_value. I then reference my data range in B1 as table_array. With an inserted MATCH function, I dynamically grab the row_index_num.

With this setup, when I update the zone in cell C9, the HLOOKUP formula uses the new zone to fetch the correct value without any need for manual formula updates, all thanks to the versatility of my horizontal lookup ally, the HLOOKUP function.

Just swap the value using the drop-down list, and there you go—the needed data appears like magic.

8. HYPERLINK Function

Syntax

HYPERLINK(link_location,[friendly_name])

Arguments

• link_location: This is where I specify the target of my hyperlink. It’s basically telling Excel where to go when the hyperlink is clicked. It breaks down into two parts:

  • link: Could be a cell address or range, either in the same sheet, a different one, or even a different workbook. I can also hyperlink to a bookmark in a Word document.
  • location: This can be a link to a file on a hard drive, a server (with a UNC path), or a URL to a website. When it comes to the Excel online version, I’m limited to web addresses. I always remember to use text format with addresses (like “https://” for web URLs) or reference a cell with the link as text.

• [friendly_name]: This part is optional, but quite useful—as it’s the text that will appear as my hyperlink. If I skip it, Excel will default to displaying the link_location. Some points to note:

  • Text, numbers, or a combination of both can be used here.
  • It can also reference another cell that has the display text I want to use.
  • Should friendly_name result in an error, that’s what will be displayed by the function.

Notes

• Web Address Files: I can hyperlink to documents hosted online, which can make it easier to share files collaboratively.

• Local Files: Not everything happens on the cloud, so Excel lets me link to files stored on the hard drive for quick access from within a spreadsheet without the need to dig through folders.

• Word Documents: Excel’s HYPERLINK function lets me link directly to a Word document, or even a specific part of it with a bookmark. Handy for referencing additional documentation or resources.

• Raw Links: If I want users to see the full URL or path, I simply omit the friendly_name. The link_location will be displayed in the cell.

9. INDIRECT Function

Syntax

I use the INDIRECT function like this:

INDIRECT(ref_text, [a1])

Arguments

Here’s what I feed into the INDIRECT function:

• ref_text: This is where I put the address or name of the cell or range I’m talking about. It could be ‘A1’, ‘B10’, or a named range like ‘MyRange’.
• [a1]: This tells INDIRECT what style of reference I’m using. If it’s A1 style, I can use TRUE or 1. For R1C1 style, FALSE or 0 does the trick. If I leave it blank, it defaults to A1 style.

Notes

Just a few things to keep in mind:

• If I’m aiming at another workbook, that workbook should be open.
• INDIRECT won’t adjust if I add rows or columns.
• To include text in the function, I need to wrap it in quotation marks. Alternatively, I can point INDIRECT to a cell that contains the text I want as a reference.

Example

Here are a couple of ways I use INDIRECT:

1. Reference to Another Worksheet:

   Say I’ve got the worksheet name in A2 and the cell reference in B2. I can create a formula in C2 like:

   =INDIRECT("'"&A2&"'!"&B2)
   

   This formula means INDIRECT will get the cell A1 value from Sheet1. If Sheet1’s A1 says “Yes”, INDIRECT gives me “Yes” too! Handy if I change the worksheet name or the cell address.

   

2. Reference to Another Workbook:

   Here’s an example formula to pull data from another workbook:

   =INDIRECT("["&A2&"]"&B2&"!"&C2)
   

   I got all the pieces I need – workbook in A2, worksheet in B2, cell in C2. Combine ’em right, and INDIRECT gets me the value from Book1’s cell A1.

   

3. Using Named Ranges:

   I can also point INDIRECT at named ranges. Let’s say I’ve called B2 “Quantity” and C2 “Amount”. If I have a dropdown in E1 and choose “Quantity” or “Amount”, INDIRECT refers to that range – then it’s a snap to add things up or make calculations.

   

11. LOOKUP Function

Syntax

When I need to retrieve a value from a specific place within a row or column, I use the LOOKUP function like so:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Here’s how I break it down:

• Vector Form: Lookup within a one-dimensional range.
• Array Form: Lookup within a multi-row array.

Arguments

To make the best out of the LOOKUP function, keep in mind:

• lookup_value: The value I’m after, which should exist in the lookup_vector.
• lookup_vector: The single row or column range where I expect to find the match for my lookup_value.
• [result_vector]: Here’s where I tell Excel where to grab the corresponding return value if there’s a match. It’s handy but not mandatory.

Notes

From personal experience, while the LOOKAP function is straightforward, I often opt for VLOOKUP or HLOOKUP as they’re more versatile, especially when dealing with tables.

12. MATCH Function

Syntax

I use the syntax MATCH(lookup_value, lookup_array, [match_type]) to find the position of a value in a list. Here’s the breakdown of its parts:

Arguments

Let’s look at each component:

• lookup_value: This is the value I’m searching for in the list.
• lookup_array: This is the set of cells where I’m hunting for the value.
• [match_type]: By picking -1, 0, or 1, I’m setting how the search should work. Each choice behaves differently:
  • Choose 1 for the largest value that’s no bigger than the lookup_value, in an ascending order list.
  • Opt for -1 to find the smallest value that’s at least as large as the lookup_value, also in an ascending order list.
  • Use 0 when I need an exact match of the lookup_value in the list.

Notes

• Wildcards can come into play for more complex searches.
• If there’s no match at all, expect to see #N/A.
• Remember, the MATCH function doesn’t care about uppercase or lowercase differences.

Example

Say I’m on a quest to find the value 5. I set the match type to 1. What happens is, I end up with the position number of the nearest value that doesn’t exceed 5. In a list with values of 2, 3, and 4, the highest is 4. It sits in position 3, so that’s the number returned to me.

13. OFFSET Function

Syntax

OFFSET(reference, rows, cols, [height], [width])

Arguments

• reference: This is where I’m starting off. I could pick a single cell or a bunch stuck together.
• rows: This tells me how many steps up or down to take from where I started. Heading down? I’ll put a plus here. Going up? That’s a minus for me.
• cols: Similar deal, but I’m moving sideways. Positive numbers take me to the right, and negative numbers take me left.
• [height]: Optional. How tall the range I’m dealing with should be.
• [width]: Also optional. This one’s for how wide the range gets.

Notes

• Keep in mind that I’m pretty temperamental. I recalculate whenever anything changes.
• If I try reaching outside the worksheet, I’m just going to show you a #REF! It’s my way of saying something’s off.
• Leaving out height or width? No worries. I’ll just assume you meant the same size as the starting point.

Example

Let’s say I want to see how a product did over several months. I’ll pile up the sales numbers with SUM and make it slick by having OFFSET grab the range. Take a peek at the dynamic duo in action right here:

14. ROW Function

Syntax

ROW([reference])

Arguments

• reference: This could be a single cell like A1 or a range like A1. It tells me which cell’s row number I want to find.

Notes

• We’re not just talking any sheet; it works on all sorts, even those sneaky hidden ones.
• Leave reference blank, and it’ll tell me the row number of where I’m at.
• A wrong sheet name? Expect a #N/A error.
• Botch the sheet reference? That’s a #REF! error heading my way.

Example

So, the first image shows how I’ve called the ROW function without an argument to get the row number of the cell where the function is entered.

Next up, look at how the ROW function can grab the row number from any cell I point it to, like this example where I’m referring to cell A5.

Now for something cool: creating a serial number list. Just pop the ROW function into a cell, drag it through, and bam, serial numbers for days.

15. ROWS Function

Syntax

ROWS(array)

Arguments

• array: The reference to a range of cells for which I want to count the total number of rows.

Notes

• Named ranges are also perfect for this.
• Just remember, it doesn’t matter what the cells contain, I’m just counting rows here.

Example

I used ROWS(A1:A10) on a column of 10 cells, and, sure enough, it gave me the count as 10. Here’s how that looked:

16. TRANSPOSE Function

Syntax

=TRANSPOSE(array)

Arguments

• array: The matrix or cell range I want to shift in orientation.

Notes

• To utilize the TRANSPOSE function effectively, I must highlight the exact number of cells that correspond to the array size and press Ctrl + Shift + Enter.
• If I select fewer cells than the original array, only the corresponding section will be flipped.

Example

Let’s say I’ve got data in the cell range B2. I want to reflect this data along the ‘diagonal’ axis onto a new range, G2. Here’s how I do it:

1. Select cell range G2.
2. Enter =TRANSPOSE(B2:D4) into G2.
3. Hit Ctrl+Shift+Enter.
4. Marvel as the rows and columns swap places!

The resulting layout shows the original rows as columns and vice versa, and remember, it’s an array function—I can’t modify individual cells within it!