combining text with an array formay

[howard]

I have tried to combine the text with an array formula, but can't get it right

 ="The Sales Report as at "&B1 "&INDEX(D:D,MATCH(1,SEARCH("*grand*",A:A)))

It would be appreciated if someone could assist me

 

[mikerickson]

What is that formula supposed to do?
What output do you want from what inputs?

BTW, the SEARCH is looking for "*grand*" in the cells, the * is not acting like a wildcard in that usage.

 

[howard]

The formula must return Sales Report for June 2018 (cell B1) and the value of Array formula for eg Sales Report for June 2018 amounts to $ 915250


I inadvertently forgot to add text "amounts to"

 

[mikerickson]

That's no help at all.
What is your data lay-out and what values should produce what results?

 

[howard]

See Sample Data below to give a clearer picture

Book1
	A	B	C	D
1	BR1	Jun-2018		-R62,863.41
2
3	Ref			Sales
4	KL-001			-1,770.00
5	KL-002			-28,714.84
6	KL-003			-17,881.59
7	KL-004			-66.67
8	KL-005			-5,500.00
9	KL-006			-16.67
10	KL-007			-266.67
11	KL-008			-17.33
12	KL-009			-9,333.33
13	KL-010			703.68
14	Grand Total			-62,863.41
15
Sheet1

Cell Formulas
Range	Formula
D1	{=INDEX(D:D,MATCH(1,SEARCH("*grand*",A:A)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mikerickson]

I don't see the string "grand" anywhere in that data set.
In words, what do you want that formula to do?

 

[kvsrinivasamurthy]

Try this modified formula

="The Sales Report as at "&B1&INDEX(D:D,MATCH(1,SEARCH("*grand*",A:A)))

Remove the extra space at the beginning of formula

 

[howard]

Thanks for the help, much appreciated