Searching and pasting column text

[NAZ2008]

Hello,

I am new to Excel and my brain is fried from trying to search the forum for similar questions/answers.

I am trying to populate a column with data based on information from 2 other columns.

In the attached worksheet, I want to populate column E with the names of the clients based on data from Column D and it would be referenced from any of the 1st 3 columns.

I tried this formula =IF(ISNUMBER(SEARCH($D$2:$D$21588,C3:C21589)),C3:C21588,"") but got the #SPILL error

Client ID	Client Name	Merged	Client ID	Client Name
ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
DEF12345	Client 2	DEF12345Client 2	ABC12345	Client 1
DEF12345	Client 2	DEF12345Client 2	ABC12345	Client 1
ABD78945	Client 3	ABD78945Client 3	ABC12345	Client 1
ABD78945	Client 3	ABD78945Client 3	DEF12345
ABD78945	Client 3	ABD78945Client 3	DEF12345
ABD78945	Client 3	ABD78945Client 3	DEF12345
ABD78945	Client 3	ABD78945Client 3	ABD78945
ABD78945	Client 3	ABD78945Client 3	ABD78945
ABD78945	Client 3	ABD78945Client 3	ABD78945
ABD78945	Client 3	ABD78945Client 3	ABD78945
ABD78945	Client 3	ABD78945Client 3	ABD78945
DEG78945	Client 4	DEG78945Client 4	ABD78945
DEG78945	Client 4	DEG78945Client 4	ABD78945
DEG78945	Client 4	DEG78945Client 4	ABD78945
DEG78945	Client 4	DEG78945Client 4	ABD78945
DEG78945	Client 4	DEG78945Client 4
DEG78945	Client 4	DEG78945Client 4
DEG78945	Client 4	DEG78945Client 4

Any help & guidance would be greatly appreciated.

 

[Cubist]

The #SPILL error means you're not giving the formula enough cell real estate to output a dynamic range of cells. Try this.
]

Book1
	A	B	C	D	E
1	Client ID	Client Name	Merged	Client ID	Client Name
2	ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
3	ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
4	ABC12345	Client 1	ABC12345Client 1	ABC12345	Client 1
5	DEF12345	Client 2	DEF12345Client 2	ABC12345	Client 1
6	DEF12345	Client 2	DEF12345Client 2	ABC12345	Client 1
7	ABD78945	Client 3	ABD78945Client 3	ABC12345	Client 1
8	ABD78945	Client 3	ABD78945Client 3	DEF12345	Client 2
9	ABD78945	Client 3	ABD78945Client 3	DEF12345	Client 2
10	ABD78945	Client 3	ABD78945Client 3	DEF12345	Client 2
11	ABD78945	Client 3	ABD78945Client 3	ABD78945	Client 3
12	ABD78945	Client 3	ABD78945Client 3	ABD78945	Client 3
13	ABD78945	Client 3	ABD78945Client 3	ABD78945	Client 3
14	ABD78945	Client 3	ABD78945Client 3	ABD78945	Client 3
15	ABD78945	Client 3	ABD78945Client 3	ABD78945	Client 3
16	DEG78945	Client 4	DEG78945Client 4	ABD78945	Client 3
17	DEG78945	Client 4	DEG78945Client 4	ABD78945	Client 3
18	DEG78945	Client 4	DEG78945Client 4	ABD78945	Client 3
19	DEG78945	Client 4	DEG78945Client 4	ABD78945	Client 3
20	DEG78945	Client 4	DEG78945Client 4		not found
21	DEG78945	Client 4	DEG78945Client 4		not found
22	DEG78945	Client 4	DEG78945Client 4		not found
Sheet11

Cell Formulas
Range	Formula
E2:E22	E2	=XLOOKUP(D2,$A$2:$A$22,$B$2:$B$22,"not found")

 

[NAZ2008]

Thank you so much for your help on this. I thought if I included more rows than I needed would do the trick.

For my own curiosity, was the original formula kind of on the right track if I had done it correctly? And by cell real estate, is the more rows or columns or the width of the column only?

 

[Cubist]

Your initial formula is about half correct. You're getting a spill error because, for each row, you're returning all the results searched in C. So the results overlap (see columns E and F below). A few additional steps are needed to return only 1 cell instead of a whole range.

Book3
	A	B	C	D	E	F	G
1	Client ID	Client Name	Merged	Client ID	Row 2 Result only	Row 3 Result only	With FILTER()
2	ABC12345	Client 1	ABC12345Client 1	ABC12345	ABC12345		ABC12345Client 1
3	ABC12345	Client 1	ABC12345Client 1	ABC12345	ABC12345	ABC12345	ABC12345Client 1
4	ABC12345	Client 1	ABC12345Client 1	ABC12345	ABC12345	ABC12345	ABC12345Client 1
5	DEF12345	Client 2	DEF12345Client 2	ABC12345		ABC12345	ABC12345Client 1
6	DEF12345	Client 2	DEF12345Client 2	ABC12345			ABC12345Client 1
7	ABD78945	Client 3	ABD78945Client 3	ABC12345			ABC12345Client 1
8	ABD78945	Client 3	ABD78945Client 3	DEF12345			DEF12345Client 2
9	ABD78945	Client 3	ABD78945Client 3	DEF12345			DEF12345Client 2
10	ABD78945	Client 3	ABD78945Client 3	DEF12345			DEF12345Client 2
11	ABD78945	Client 3	ABD78945Client 3	ABD78945			ABD78945Client 3
12	ABD78945	Client 3	ABD78945Client 3	ABD78945			ABD78945Client 3
13	ABD78945	Client 3	ABD78945Client 3	ABD78945			ABD78945Client 3
14	ABD78945	Client 3	ABD78945Client 3	ABD78945			ABD78945Client 3
15	ABD78945	Client 3	ABD78945Client 3	ABD78945			ABD78945Client 3
16	DEG78945	Client 4	DEG78945Client 4	ABD78945			ABD78945Client 3
17	DEG78945	Client 4	DEG78945Client 4	ABD78945			ABD78945Client 3
18	DEG78945	Client 4	DEG78945Client 4	ABD78945			ABD78945Client 3
19	DEG78945	Client 4	DEG78945Client 4	ABD78945			ABD78945Client 3
Sheet7

Cell Formulas
Range	Formula
E2:E19	E2	=IF(ISNUMBER(SEARCH(D2,$C$2:$C$19)),D2,"")
F3:F20	F3	=IF(ISNUMBER(SEARCH(D2,$C$2:$C$19)),D2,"")
G2:G19	G2	=UNIQUE(FILTER($C$2:$C$19,ISNUMBER(SEARCH(D2,$C$2:$C$19))))
Dynamic array formulas.