Explanation
In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found. In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:
=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))
Working from the inside out, this formula is based on the formula described here , which uses the SEARCH function together with the ISNUMBER function :
ISNUMBER(SEARCH(things,B5))
In a nutshell, SEARCH returns a number if it finds the target string and an error if not, and ISNUMBER converts this result into a TRUE or FALSE. In this case, we are looking for several strings at once – the five colors in the named range things (E5:E10), so the code above returns an array with five TRUE FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;TRUE}
Notice the TRUE values correspond to white and green in the list of colors (positions 3 and 5), and FALSE values correspond to the colors not found. This array is returned directly to the FILTER function as the include argument, with the array argument given as things:
FILTER(things,{FALSE;FALSE;TRUE;FALSE;TRUE},"")
The FILTER function uses the TRUE and FALSE values to filter the list of colors and returns an array of the two colors found:
{"white";"green"} // result from FILTER
The result from FILTER is delivered to the TEXTJOIN function :
=TEXTJOIN(", ",1,{"white";"green"})
TEXTJOIN is configured to join items in the array with a comma and space (", “) and to ignore empty strings (”"). The number 1 is provided instead of TRUE for brevity. TEXTJOIN concatenates the found colors, separated by commas, and returns a final result.
Note FILTER’s not_found argument is provided as an empty string (""). In the event no colors are found, FILTER returns an empty string to TEXTJOIN, which then also returns an empty string as the final result.
Explanation
The goal is to retrieve the value of a cell at a given row and column location, which are entered in cells G5 and G4, respectively. There are several ways to go about this, depending on your needs. See below for options.
ADDRESS and INDIRECT
In the example worksheet, the ADDRESS function is combined with the INDIRECT function to solve this problem with the following formula:
=INDIRECT(ADDRESS(G4,G5))
The ADDRESS function returns the address for a cell based on a given row and column number like this:
=ADDRESS(1,1) // returns $A$1
=ADDRESS(1,2) // returns $B$1
=ADDRESS(2,1) // returns $A$2
=ADDRESS(2,2) // returns $B$2
Note: ADDRESS can return references in different formats, see here for details.
In the example shown, the ADDRESS function returns the value “$C$9” inside INDIRECT:
=INDIRECT(ADDRESS(9,3))
=INDIRECT("$C$9")
The INDIRECT function converts a text value into a valid reference. INDIRECT converts the text “$C$9” into the cell reference $C$9 and returns “Mango” as the final result:
=INDIRECT("$C$9")
=$C$9
="Mango"
While this formula works, there is a better way to retrieve the cell value at a known location in Excel.
Note: INDIRECT is a volatile function and can cause performance problems in more complicated worksheets.
INDEX function
The INDEX function is well-known in Excel because it is part of INDEX and MATCH , a common way to perform lookup operations in Excel. But INDEX can also be used by itself to retrieve values at known coordinates. To solve this problem, we can give the INDEX function a range that begins at A1, and includes the cells that need to be referenced like this:
=INDEX(A1:E100,9,3) // returns "Mango"
As before, the result is “Mango” and this is a much better example of how to retrieve a value in Excel. Replacing the hardcoded row and column numbers above with worksheet references we have:
=INDEX(A1:E100,G4,G5)
This formula returns the same result seen in the screenshot. If row or column numbers change, the INDEX function returns a new result. Note that the size of the range we give INDEX is arbitrary, but it must start at A1 and include the data you wish to reference.
