Author: Oscar Cronquist 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.



Create named ranges:

  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

(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