Change font size when concatenating text

[ilcaa]

hello, i am concatenating text within a cell with formula and references to text along with text... is there a way to change the size of my Text in various portions of my formula?

=VLOOKUP($B$3,'sample dashboard'!$D$4:$AG$116,24,FALSE) & " Top Tiers Ordered" & " National Average" & $A$1

I want to change the font size at the end (" National Average" & $A$1) to 12 and front portion (=VLOOKUP($B$3,'sample dashboard'!$D$4:$AG$116,24,FALSE) & " Top Tiers Ordered") to 15 Font

is this possible... how?

 

[kevin9999]

I'm not aware of any method of doing this using formulas alone - I think you need a VBA solution, although I'm happy to be proven wrong.
The following puts the formula into cell D3 on "Sheet1" returning a Vlookup value from the sheet "sample dashboard" - change these parameters to suit.

Option Explicit
Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet, s As String, n As Long, x As Long
    Set ws1 = Worksheets("Sheet1")              '<~~ *** Change sheet names as required ***
    Set ws2 = Worksheets("sample dashboard")
    
    With ws1.Range("D3")
        .Formula = "=VLOOKUP($B$3,'sample dashboard'!$D$4:$AG$116,24,FALSE) & "" Top Tiers Ordered"" & "" National Average"" & $A$1"
        .Value = .Value
        s = .Value
        .Font.Size = 15
        n = InStr(s, " National")
        For x = n To Len(s)
            .Characters(x, 1).Font.Size = 12
        Next x
    End With
End Sub