Adding additional text to a value pulled from another sheet

[T1991]

Hi,

I am using the following formula to pull names from another sheet that meet a certain criteria. It will only pull a name once, before moving onto the next match. However, for each name pulled, I want to add the word " Replacement" at the end of it.

Is that possible?

The formula I am using is:
=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1)),"")

I tried adding &" Replacement" at the end but not only does it cause the formula to start repeating names, but when dragged across a range, it fills every cell with the word Replacement. I only want the word to appear against a pulled value.

 

[Snakehips]

@T1991 Does this help?

=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1))&" Replacement","")

 

[T1991]

@T1991 Does this help?

=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1))&" Replacement","")

Thank you for responding.

It sort of works. When I drag the formula down to fill the range, so C11 through to C20 for example, the name that was pulled into C11 repeats with the word Replacement at the end. However, I wanted the formula to only pull a name from Master - column G once before finding the next name that meets the criteria for the other cells. So every entry within the range is unique. If it can't find a name, it just leaves it blank.

When the " Replacement" part is removed, it does just that. But the moment it is added, the formula seems to lose that functionality and just repeats the first name.

Hopefully that makes sense.

 

[Snakehips]

It may help If you are able to use XL2BB to post an illustration of your typical data and the result you are expecting.

 

[T1991]

This is the Master sheet, where I want to pull values from column G:

Template v.2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	< Input data
2	DEPARTMENT	REQUEST ID	POSITION NUMBER	SOURCE	BACKFILL / ADDITIONAL	APPROVED FROM	FORMER ROLE OCCUPANT	OU SHORT-CODE	CONTRACT TYPE	END DATE (MM/YY)	MANAGER	PROPOSED OU	PROPOSED OU SHORT-CODE	PROPOSED POSITION TITLE	PROPOSED ROLE GRADE	COMMENTS	RECRUITMENT FORECAST (MM/YY)	STATUS	RISKS IF NOT FILLED IN 2024	CONFIRM RECRUITMENT PLAN FOR 2024	RECRUITMENT STATUS - DEC REVIEW	RECRUITMENT STATUS - APR REVIEW	RECRUITED CANDIDATE	START DATE (MM/YY)
3	... | P
4	... | PC
5	... | PL
6	... | PS
7	... | PS	M	N	O	Backfill	P	Test 2	PSS	Exec	01/24
8	... | PS	I	J	K	Backfill	L	Test 1	PSK	Exec	01/24
9	... | PS	E	F	G	Backfill	H	John Doe	PS	Indirect	01/24
10	... | PS	A	B	C	Backfill	D	Joe Bloggs	PS	Exec	01/24			PS				01/24						Test 3	01/24
11	... | PP
12	... | PM
13	... | PI
14	... | PV
Master

This is the supporting sheet, where I want the values to pull to:

Template v.2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	…	…			…		47	…		47
2	Memo:	…
3
4	01/24										…			…
5	AOR
6	Totals	Exec			Staff			Indirect			Direct			External
7
8
9		-3	0	1	0	0	0	-1	0	0	0	0	0	0	0	0
10		-1	0	1	-1	0	1	-1	0	1	-1	0	1	-1	0	1
11	PS	Joe Bloggs		Test 3				John Doe
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Support

Cell Formulas
Range	Formula
B9,N9,K9,H9,E9	B9	=0-COUNTIF(B11:B110,"?*")
D9,P9,M9,J9,G9	D9	=COUNTIF(D11:D110,"?*")
D11:D30	D11	=IFERROR(INDEX(Master!$W:$W,AGGREGATE(15,6,ROW(Master!$W$3:$W$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$X$3:$X$102=$A$4)/(ISNA(MATCH(Master!$W$3:$W$102,D$10:D10,0))),1)),"")
E11:E30	E11	=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$E$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,E$10:E10,0))),1)),"")
G11:G30	G11	=IFERROR(INDEX(Master!$W:$W,AGGREGATE(15,6,ROW(Master!$W$3:$W$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$E$6)/(Master!$X$3:$X$102=$A$4)/(ISNA(MATCH(Master!$W$3:$W$102,G$10:G10,0))),1)),"")
H11:H30	H11	=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$H$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,H$10:H10,0))),1)),"")
J11:J30	J11	=IFERROR(INDEX(Master!$W:$W,AGGREGATE(15,6,ROW(Master!$W$3:$W$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$H$6)/(Master!$X$3:$X$102=$A$4)/(ISNA(MATCH(Master!$W$3:$W$102,J$10:J10,0))),1)),"")
K11:K30	K11	=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$K$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,K$10:K10,0))),1)),"")
M11:M30	M11	=IFERROR(INDEX(Master!$W:$W,AGGREGATE(15,6,ROW(Master!$W$3:$W$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$K$6)/(Master!$X$3:$X$102=$A$4)/(ISNA(MATCH(Master!$W$3:$W$102,M$10:M10,0))),1)),"")
N11:N30	N11	=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$N$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,N$10:N10,0))),1)),"")
B11:B30	B11	=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,B$10:B10,0))),1)),"")
P11:P30	P11	=IFERROR(INDEX(Master!$W:$W,AGGREGATE(15,6,ROW(Master!$W$3:$W$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$N$6)/(Master!$X$3:$X$102=$A$4)/(ISNA(MATCH(Master!$W$3:$W$102,P$10:P10,0))),1)),"")

The columns with a yellow "0" header is where I want to pull the names from Master - column G to. Using the formula in the column adjacent to it, the one with a -1 header, I can do just that. However, for any value in this column, I want to add " Replacement" to it. However, whereas in the -1 column, only a singular instance of a value is pulled, when I drag the formula from the last response down to cover the entire cell, it repeats the name from the first cell continuously for the entire column.

 

[T1991]

It may help If you are able to use XL2BB to post an illustration of your typical data and the result you are expecting.

Hopefully the XL2BB illustration makes sense. Any help would be much appreciated

 

[kvsrinivasamurthy]

Try

=IFERROR(INDEX(Master!$G:$G &" Replacement",AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1)),"")

 

[kvsrinivasamurthy]

In D11

=IFERROR(INDEX(Master!$W:$WG &" Replacement",AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1)),"")

In E11

=IFERROR(INDEX(Master!$G:$G &" Replacement",AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1)),"")

Keep F11 blank.
Select D11:F11. Drag across using fill handle till P11
For B11 fill separately.

=IFERROR(INDEX(Master!$G:$G &" Replacement",AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$M$3:$M$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$Q$3:$Q$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102,C$10:C10,0))),1)),"")

Copy down entire row using fill handle suitably.

 

[Snakehips]

@T1991 Maybe give the below a try in B11 and drag down.
If it works then just adapt the principle for your other columns.

=IFERROR(INDEX(Master!$G:$G&" Replacement",AGGREGATE(15,6,ROW(Master!$G$3:$G$102)/(Master!$H$3:$H$102=$A$11)/(Master!$I$3:$I$102=$B$6)/(Master!$J$3:$J$102=$A$4)/(ISNA(MATCH(Master!$G$3:$G$102&" Replacement",B$10:B10,0))),1)),"")