LOOKUP Articles: Excel and Google Sheets

We can find the sum of a range with the INDEX function. The steps below will walk through the process. Figure 1: Result of the SUM of a Range (THE ROWS) with the INDEX Function Setting up the Data We will set up the data following the scheme in figure...

Based on some unique data values we can match and retrieve the relevant information from a large dataset using various functions, such as VLOOKUP. The required information can be stored in the same worksheet, a different worksheet or even a worksheet of a completely different workbook in Excel. Here, we...

Excel allows a user to specify a self-contained lookup table for VLOOKUP function. Instead of passing a table from the Sheet to the VLOOKUP, we can put a range directly in the function. This step by step tutorial will assist all levels of Excel users in creating the self-contained VLOOKUP...

The Wildcard character is used to signify a dummy character to substitute the actual character in the text. So, ultimately you won’t need to search the actual text but can look up the partial text. What is a Partial match against numbers with a wildcard in Excel? The Partial Match...

We often work with ranges in Excel where we need to find the largest value based on certain criteria. The MAX IF criteria match helps us to find the largest value within a range of cells based on a specific condition. In this tutorial, we will learn how to use...

In Excel, a 2D array has values along cell rows and columns. To find the location of any particular value in the 2D array, the SUMPRODUCT function should be used. Formula =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1 How to Get Location of Value in 2D Array In order to get the location of a...

In Excel, we are able to lookup an entire column with the INDEX and MATCH functions.  The INDEX function returns a value as specified from within a range, while the MATCH function returns the position of a value in a range. This step by step tutorial will assist all levels...

Figure 1. of the Index and Match Operation in Excel. In order to determine the value of the first match in an array of data, with the use of a Wildcard, we can utilize an Index and Match operation syntax, configured to find an exact match. Generic Formula =INDEX(range,MATCH(val&"*",range,0)) How...