Objectiveīefore we get started, let me quickly cover the double-click shortcut.
#Select large range in microsoft excel for mac without scrolling how to#
In the image below, you can see that in a search for the value 646 in column F, MATCH returns 4.Are you familiar with the double-click shortcut to fill formulas down? If so, have you noticed it stops filling down at the first blank row? This post will discuss the double-click shortcut as well as a simple workaround for how to fill it down through a report range even when there are blank rows in the middle. MATCH takes a search term and returns a cell reference. It might not be clear how we're going to use these two functions together, so I'll lay it out here. 0 will only return your exact term, and -1 will return the smallest value that is greater than or equal to your search term. 1 will return the largest value that is smaller than or equal to your search term. is an optional argument that can be 1, 0, or -1.is the array in which Microsoft Excel will look for the search term.is a search term that can be a string or a number.and can be used to narrow your search (we'll take a look at that in a moment.).is the array in which you'll be searching.Both functions have drawbacks, but by combining them we'll build on the strengths of both.įirst, though, the syntax of both functions: INDEX and MATCH are two different functions, but when they're used together they can make searching a large spreadsheet a lot faster. Here's how we'll do it:Īs you can see in the image below, the score is returned: You can use HLOOKUP to find the average score in Minnesota in 2013. This spreadsheet contains a row for each state, along with an SAT score in the years 2000–2014. is the same as in VLOOKUP, leave it blank to get the nearest value when possible, or enter 0 to only look for exact matches.specifies the row that the return value will come from.is the cells in which you want to search.is the value that you know and want to find a corresponding value for.Because it's usually easiest to scan through column headings until you find the right one and use a filter to find what you're looking for, HLOOKUP is best used when you have really big spreadsheets or you're working with values that are organized by time.
Where VLOOKUP finds corresponding values in another column, HLOOKUP finds corresponding values in a different row. So in the image below, a VLOOKUP looking for an SAT score of 652 will return 646, as it's the closest number in the list that's less than 652, and defaults to 1. If you enter 1 or omit this argument, the function looks for the value you entered or the next-lowest number.
is the piece of information that you already have.
Two examples where you might do this are looking up an employee's last name by their employee number or finding a phone number by specifying a last name. This function allows you to specify a column and a value, and will return a value from the corresponding row of a different column (if that doesn't make sense, it'll become clear in a moment). Once you know how to search in Excel using lookup, it wont matter how big your spreadsheets get, you'll always be able to find something in Excel! If you're working with a really big spreadsheet, it can save a lot of time to use one of these four lookup functions. If you can't just scan through the rows and columns for it, you can use Ctrl + F to search for it. Most of the time, searching a Microsoft Excel spreadsheet is pretty easy.
0 kommentar(er)
