Auto-filling the address column with info from data sheet

[Hunk1]

Hello there,

I want excel to search up a address and fill up the numbers according to people living on the house, however i crashed trying to do the formula i stopped at:

=IF(AND(OR(COUNT.IF('Folha de Assinatura'!C10;"RUA*")=1;(COUNT.IF('Folha de Assinatura'!C10;"AVENIDA*")=1));if('Folha de Assinatura'!C10<>""'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12;"")

the Source Sheet: the address is on column c; i though about doing a IF function to search for "Rua*" or "Avenida*", however i crashed trying to do a simplier function
that would not have like 20 IFs attached to itself:

FOLHA ASS 2022 m03 03-02-2023.xlsm
	A	B	C	D	E	F	G	H
10			RUA NATERCIA 37504080
11	FA	NOME	Nº	D. NASC.	CARTÃO DO SUS	DOENÇA	DATA VD	ASSINATURA
12	159	BENEDITO GONCALVES FILHO	90	10/05/1951	702103775242996	HA/DIA*
13
14	160	TERESINHA DE FATIMA DA SILVA	60	01/04/1973	708504337827477	FU
15		RICARDO MARCOS DE ARAUJO		18/06/1971	772.478.686-00	HA/FU
16		FILIPE MARCOS ARAUJO		02/07/1993	117.995.396-79	FU
17
18			AVENIDA PRESIDENTE TANCREDO NEVES 37504066
Folha de Assinatura

the Destination Sheet:

FOLHA ASS 2022 m03 03-02-2023.xlsm
	B	C	D	E	F	G	H
9	NOME	D. NASC.	ANO NASC	CONDIÇÃO	SEXO	Quem é o Chefe?	ENDEREÇO
10	BENEDITO GONCALVES FILHO	10/05/1951	1951	HA/DIA*	M	BENEDITO GONCALVES FILHO	RUA NATERCIA 37504080 , 90
11	0	0	0	0	0
12	TERESINHA DE FATIMA DA SILVA	01/04/1973	1973	FU	F	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 , 60
13	RICARDO MARCOS DE ARAUJO	18/06/1971	1971	HA/FU	M	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 , 60
14	FILIPE MARCOS ARAUJO	02/07/1993	1993	FU	M	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 ,
Planilha para Roteiro

Cell Formulas
Range	Formula
B10:B14	B10	='Folha de Assinatura'!B12
C10:C14,E10:E14	C10	='Folha de Assinatura'!D12
D10:D14	D10	=RIGHT(C10,4)
F10:G14	F10	='Folha de Assinatura'!I12
H10	H10	=IF(AND(COUNTIF('Folha de Assinatura'!C10,"RUA*")),'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12,"")
H12,H14	H12	=IF(AND(A12<>"",B12<>""),'Folha de Assinatura'!$C$10&" , " &'Folha de Assinatura'!C14,IF(B12<>"",'Folha de Assinatura'!C12&" , " &'Folha de Assinatura'!C14,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E10:E1089	Cell Value	="SEM DOEN."	text	NO
D10:D1089	Cell Value	between 2004 and 2023	text	NO
F9:G9	Cell Value	contains "f"	text	NO
F9:G9	Cell Value	contains "M"	text	NO
E9:E1089	Cell	does not contain a blank value	text	NO
B10:B1089	Cell Value	duplicates	text	NO
B10:B1089	Cell Value	duplicates	text	NO
B10:B1089	Cell Value	duplicates	text	NO

The expected Result:

FOLHA ASS 2022 m03 03-02-2023.xlsm
	B	C	D	E	F	G	H
9	NOME	D. NASC.	ANO NASC	CONDIÇÃO	SEXO	Quem é o Chefe?	ENDEREÇO
10	BENEDITO GONCALVES FILHO	10/05/1951	1951	HA/DIA*	M	BENEDITO GONCALVES FILHO	RUA NATERCIA 37504080 , 90
12	TERESINHA DE FATIMA DA SILVA	01/04/1973	1973	FU	F	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 , 60
13	RICARDO MARCOS DE ARAUJO	18/06/1971	1971	HA/FU	M	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 , 60
14	FILIPE MARCOS ARAUJO	02/07/1993	1993	FU	M	TERESINHA DE FATIMA DA SILVA	RUA NATERCIA 37504080 , 60
19	RICARDO SIQUEIRA DE CARVALHO	27/08/1967	1967	FU/SM/AL/DR	F	RICARDO SIQUEIRA DE CARVALHO	RUA NATERCIA 37504080 , 60
20	JESSICA DE CARVALHO	16/11/1992	1992	FU/SM	0	RICARDO SIQUEIRA DE CARVALHO	RUA NATERCIA 37504080 , 60
27	MARIA AUXILIADORA SALES GOMES	31/12/1944	1944	HA	F	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
28	MARIA WANDERLEIA GOMES	29/04/1971	1971	sem comorb.	F	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
29	ADRIANA GOMES	18/06/1973	1973	sem comorb.	F	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
30	ANDREIA DE CASSIA GOMES	14/06/1973	1973	DIA	M	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
31	CRISTINA PIEDADE GOMES PEIXOTO	11/12/1968	1968	sem comorb.	0	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
32	MANOEL VITOR GOMES PEIXOTO	10/01/2006	2006	AUTISTA	0	MARIA AUXILIADORA SALES GOMES	AVENIDA PRES. TANCREDO NEVES 37504066, 750A
Planilha para Roteiro

Cell Formulas
Range	Formula
B10,B27:B32,B19:B20,B12:B14	B10	='Folha de Assinatura'!B12
C10,E27:E32,C27:C32,E19:E20,C19:C20,E12:E14,C12:C14,E10	C10	='Folha de Assinatura'!D12
D10,D27:D32,D19:D20,D12:D14	D10	=RIGHT(C10,4)
G27:G32,G19:G20,F12:G14,F10:G10	F10	='Folha de Assinatura'!I12
H10	H10	=IF(AND(COUNTIF('Folha de Assinatura'!C10,"RUA*")),'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12,"")
F19	F19	='Folha de Assinatura'!I22
F20,F27:F32	F20	='Folha de Assinatura'!I24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E10:E1089	Cell Value	="SEM DOEN."	text	NO
D10:D1089	Cell Value	between 2004 and 2023	text	NO
F9:G9	Cell Value	contains "f"	text	NO
F9:G9	Cell Value	contains "M"	text	NO
E9:E1089	Cell	does not contain a blank value	text	NO
B10:B1089	Cell Value	duplicates	text	NO
B10:B1089	Cell Value	duplicates	text	NO
B10:B1089	Cell Value	duplicates	text	NO

 

[sijpie]

Formula in H10 on the results sheet:

=IF(LEN(B10)>1,IF(LEN('Folha de Assinatura'!C12)>0,INDEX('Folha de Assinatura'!C$9:C12,MIN(IFNA(XMATCH("Ave*",'Folha de Assinatura'!C$9:C12,2,-1),10^6),IFNA(XMATCH("Rua*",'Folha de Assinatura'!C$9:C12,2,-1),10^6))) & ", " & 'Folha de Assinatura'!C12,Sheet4!I9),"")

XMATCH (and XLOOKUP) have a parameter to search from the bottom up.
So if there is a name in the B column of the results sheet, and a house number entry in the data sheet, it will find the last streetname used in the column.
If there is nu street number, it will use the number in the cell above.