Find And Retrieve Missing Values in Google Sheets

Find and retrieve is a common basic operation in Google Sheets for tables. In out daily work, we may encounter this situation that some data was lost after several operations on a table in Google Sheets. In fact, we can find the missing values by retrieving the original data and comparing it with the existing data.

In this article, we will show you how to find and retrieve the missing values in Google Sheets. Look at the following example, compared to the initial column “Alphabet“, the “Partial List” only lists some letters, some are missing. What we need to do is to find the missing values and fill them in the “partial list” after the letter “H”.

Initial columns:

FIND AND RETRIEVE missing values1

Expect result:

FIND AND RETRIEVE missing values1

General Formula in Google Sheets

The general formula for this case is as below:

=INDEX(CompleteList,MATCH(TRUE,ISNA(MATCH(CompleteList,PartialList,0)),0))

 In the general Google Sheets formula, you can replace CompleteList and PartialList with your own table. This is an array formula, we need to enter “control + shift + enter” after entering the formula.

In the above example, the formula is:

=INDEX(AL,MATCH(TRUE,ISNA(MATCH(AL,$C$2:C7,0)),0))

FIND AND RETRIEVE missing values1

The complete list is “Alphabet list” (A2:A11, named “AL”); Partial list is “Partial List” (C2:C7), this column is C2:C11 actually, but some values are missing, we need to refer to the complete list to fill in the missing values in cells C8:C11. Build above formula in C8, then copy down the formula, the missing values are pasted.

EXPLANATION

 The general formula nests multiple formulas, and we need to explain each formula from inside to outside. The core is the internal MATCH function which helps us locate the missing values.

INDEX and MATCH

First let’s get to know the MATCH function.

MATCH is a Google Sheets function for locating the position of a query value in a row, column, or table. It supports approximate and exact matches, as well as partial matches with wildcards. Typically, MATCH is used in combination with the INDEX function to retrieve a value at a matching position.

 The Syntax of the MATCH function is as below:

 =MATCH (lookup_value, lookup_array, [match_type]) (match type 0=exact match)

See the example below where MATCH returns the position of the letter “B” in the column.

=MATCH("B",A2:A11,0)

INDEX and MATCH Combination:

=INDEX(B2:B11,MATCH(E2,A2:A11,0))

In this example, we entered the formula in Cell C8:

=INDEX(AL,MATCH(TRUE,ISNA(MATCH(AL,$C$2:C7,0)),0))

Note: you need to entered “Shift+Control+Enter”, and formula returns missing letter B. You can find out letter B is the first missing letter among all missing letters in the list. As why B is returned in this cell, you can see our explanation in the following steps.

For expression MATCH(AL,$C$2:C7,0), refer to MATCH function syntax =MATCH (lookup_value, lookup_array, [match_type]), the lookup value is “AL” (A2:A11), the lookup array is $C$2:C7, as this formula will be copied down to cell C9 (until C11), so the lookup array is an extended range, the starting cell is a C2, so we add $ before row and column indexes; the ending cell is the cell “above it”, this allows the missing value returned from the formula (in cell C8 for example) is included in the next calculation (in cell C9), so we don’t need to add $ to lock the ending cell.

=ArrayFormula(MATCH(AL,$D$2:D7,0))

MATCH function will iterate through all the values in column “Alphabet List” and compares them against the column “Partial List”. It returns an array that contains numbers and #N/A errors.

In this example the result is:

 {1;#N/A;2;#N/A;4;#N/A;5;6;3;#N/A}.

This array is directly delivered to ISNA function.

=ArrayFormula(ISNA(MATCH(AL,$D$2:D7,0)))

The ISNA function is used to determine if a value is a #N/A error. if it is, it returns true, if not, it returns false. In this example, if it is true, it represents a missing value, and if it is false, it represents an existing value. Based on the returned array of MATCH function, we can get a new array that only contains TRUE and FALSE after running ISNA.

The result of ISNA function:

{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

Refer to above screenshot, we can see that ISNA is the lookup array for the outer MATCH expression.

=ArrayFormula(MATCH(TRUE,ISNA(MATCH(AL,$D$2:D8,0)),0))

For this MATCH expression:

=MATCH(TRUE,{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE},0)

MATCH function will retrieve “TRUE” value from the lookup array and returns the first matching position of TRUE value. Obviously, the first TRUE value is listed in row 2.

=INDEX(AL,2)

At last, INDEX function returns the letter in “row 2” in column AL. So letter B is returned in cell C8.

Copy down the formula to C9 (select cell C8, drag down the handler). Let’s see the result.

For the inner MATCH function, the lookup array is automatically extended to C8. Letter B is an existing letter in “Partial List”. Refer to above steps, the first missing value is D in this instance, so D is returned after running the formula.

Notice: #N/A error will be returned after filling all missing values.

 Related Functions

  • Google Sheets INDEX function
    The Google Sheets INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Google Sheets MATCH  function
    The Google Sheets MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Google Sheets and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
Related Posts

Find the Closest Data to the Data Provided in Google Sheets

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, google sheets internal functions can help us solve this problem. In today’s article, we will show you how ...

Basic Array Formula With Examples in Google Sheets

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in ...

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do? If you are new to google sheets, then your first attempt ...

Calculating Average Of The Numbers in Google Sheets
_3 Average numbers in google sheets1

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets
Average the last 3_ 5 or N numeric values in google sheets1

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Add Workdays in Google Sheets
Add workdays no weekends in google sheets1

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Sidebar