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:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to create a named range
- Select cell range Sheet2!A1:C3
- Type tbl_text in name box
- Press Enter
How to copy array formula
- Select cell B2
- Copy (Ctrl + c)
- Select cell range B2:D3
- 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