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

1. Select the range containing blanks (E2:E11).
2. Press F5
3. Click "Special..."
4. Click "Blanks"
5. Click OK!
6. 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

Related posts:

How to automatically create a unique distinct list and remove blanks

Sorting numbers and text cells also removing blanks using array formula in excel

Sorting numbers and text cells descending also removing blanks using array formula in excel

Lookup between two lists of data to highlight missing data using conditional formatting in excel

Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed