Author: Oscar Cronquist Article last updated on June 18, 2011

Rui Costa asks:

I'm using Excel 2007 and I have 2 Sheets:
____A B C D E F
bla X X X
ble X X
Name Letter1 Letter2
I'm trying to make a formula to put in the cell where is the first X after bla, that checks the Sheet 2 for name and populates with the X the letters it finds.


Your question is similar to the question in this post: Merge matching rows in excel. But this time it is about unique text values and not numbers (all).



Array formula in Sheet1, cell B2:

=IFERROR(INDEX(tbl_text, MIN(IF((COUNTIF($A2:A2, tbl_text)=0)*(INDEX(tbl_text, 0, 1)=$A2)*(tbl_text<>""), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($A2:A2, INDEX(tbl_text, MIN(IF((COUNTIF($A2:A2, tbl_text)=0)*(INDEX(tbl_text, 0, 1)=$A2)*(tbl_text<>""), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1), "")

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Named ranges

tbl_text: Sheet2!A1:C3

How to create a named range

  1. Select cell range Sheet2!A1:C3
  2. Type tbl_text in name box
  3. Press Enter

How to copy array formula

  1. Select cell B2
  2. Copy (Ctrl + c)
  3. Select cell range B2:D3
  4. Paste (Ctrl + v)

Download excel sample file for this article.

(Excel 2007 Workbook *.xlsx)

Functions in this article

ROW(reference) Returns the rownumber of a reference

Counts the number of cells within a range that meet the given condition

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

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

Returns the smallest number in a set of values. Ignores logical values and text