Rui Costa asks:

I'm using Excel 2007 and I have 2 Sheets:
Sheet1:
____A B C D E F
bla X X X
ble X X
Sheet2:
Name Letter1 Letter2
bla___A______B
ble___A______C
bla___B______A
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.

Answer:

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).

Sheet2

Sheet1

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.

Rui-Costa.xls
(Excel 2007 Workbook *.xlsx)

Functions in this article

ROW(reference) Returns the rownumber of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
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

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Related posts:

Merge matching rows in excel

Extract unique values from a range using array formula in excel

Unique distinct values from multiple columns using array formula

Extract duplicate text values from a range containing both numerical and text values in excel

Filter unique text values from a range containing both numerical and text values in excel