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.

fill-all-blanks

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.

fill-all-blanks2

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