## How to automatically fill all blanks with missing data or formula

*Article last updated on April 08, 2018*

**Question:** I have two lists. The first list contains two columns, unique values and names.

The second list contains unique values and associated names and sometimes also blanks. The second list is huge.

How do I automatically fill in the blanks in the second list? See picture below.

**Answer:**

Create named ranges**:
**unique=A2:A11

value=B2:B11

- Select the range containing blanks (E2:E11).
- Press F5
- Click "Special..."
- Click "Blanks"
- Click OK!
- Type =INDEX(value,MATCH(D3,unique,0)) in formula window and press
**CTRL**+ Enter

All blank fields have now values. See picture below.

**How to customize the formula to your workbook**

Change named ranges to your workbook.

### Download excel sample file for this tutorial

fill-blanks-with-formula.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**MATCH(**lookup_value, lookup_array, [match_type]**)**

**INDEX(**array,row_num, [column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

How to find errors in a worksheet

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

Delete blanks and errors in a list

The formula deletes blank cells and cells with errors. It doesn´t matter if the cells contain numbers or text, they […]

How to quickly select a non contiguous range

A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

How to find errors in a worksheet

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

Convert column number to column letter

Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form