Find And Retrieve Missing Values Using INDEX/MATCH

Find and retrieve is a common basic operation in excel for tables. In out daily work, we may encounter this situation that some data was lost after several operations on a table. 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. 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 RETRIEVE1

Expect result:

FIND AND RETRIEVE1

GENERAL FORMULA

The general formula for this case is

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

 In the general 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)), 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.

 INDEX and MATCH

First let’s get to know the MATCH function.

MATCH is an Excel 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.

 Syntax:

=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.

FIND AND RETRIEVE1

INDEX and MATCH Combination:

FIND AND RETRIEVE1

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.

In this example, we entered the formula =INDEX(AL,MATCH(TRUE,ISNA(MATCH(AL,$C$2:C7,0)),0)) in cell C8, 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.

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.

FIND AND RETRIEVE1

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.

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}

FIND AND RETRIEVE1

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

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.

FIND AND RETRIEVE1

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

FIND AND RETRIEVE1

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

FIND AND RETRIEVE1

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.

FIND AND RETRIEVE1

Related Functions

  • Excel INDEX function
    The Excel 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 Microsoft Excel 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])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel 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

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you're producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time ...

Calculate Loan Interest in Given Year

When you borrow money, you are supposed to repay it gradually. Lenders, on the other hand, want to be compensated for their services and the risk they incur by lending you money. That is, you will not just repay the ...

Calculate Interest Rate for Loan

The interest rate is the fee charged by a lender to a borrower and is expressed as a percentage of the principal—the lent amount. The interest rate on a loan is often expressed as an annual percentage rate, abbreviated as ...

Calculate Interest for Given Period

Using the IPMT function in Excel, we can compute the interest payment on any loan. This step-by-step tutorial will guide Excel users of all skill levels through the process to calculate interest for given period. Finally, the formula: =IPMT(B3/12,1,B5,-B2) The ...

How To Use Excel GCD Function

This post will guide you how to use Excel GCD function with syntax and examples in Microsoft excel. Description The Excel GCD function Returns the greatest common divisor of two or more integers. So you can use the GCD function ...

Calculate A Ratio From Two Numbers In Excel

In elementary mathematics, a ratio is a connection or comparison between two or more integers. For example, ratios are often expressed as ":" to demonstrate the relationship between two numbers. You would think that manually calculating a ratio from two ...

How To Use Excel RRI Function

This post will guide you how to use Excel RRI function with syntax and examples in Microsoft excel. Description The Excel RRI function Returns an equivalent interest rate for the growth of an investment. So you can use the RRI ...

CAGR Formula Examples in Excel

CAGR in Excel is a formula that calculates the compound annual growth rate for any invested amount over the specified years or timeframe. Although there is no direct function in Excel that can help us identify the CAGR value, there ...

Build Hyperlink With VLOOKUP in Excel

You might have come across a task in which you were assigned to build hyperlinks, which seems very easy, and if you are new to excel or don't have enough experience with it, then you might wonder about doing this ...

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Sidebar