Copy formatting from Sheet to Sheet while using vlookup

[muhleebbin]

I have the following data in one sheet (Attached Image) with a corresponding sheet that finds relevant data that i'm searching for.

LTWOM v2.0.xlsx
	L	M	N	O	P	Q	R
1	AoE	Attack	Buff	Cooldown	Critical	Damage Over Time	Defense
2	+	+, Fr+, Ri+, HiddenEta+	Eta-				Fr+, Eta-
3	+	+, Fr+, Ri+					Fr+, Eta-
4	+	Fr+, Ri+, Eta-		+			Fr+
5		+, Ri+
Toon Finder

Cell Formulas
Range	Formula
L1:R1	L1	=IF(ISBLANK('Toon Data'!J1),"",'Toon Data'!J1)
L2:R5	L2	=IF(ISNA(VLOOKUP($A2,'Toon Data'!$A$3:$CC$13,'Toon Data'!J$2,FALSE)),"",IF(ISBLANK(VLOOKUP($A2,'Toon Data'!$A$3:$CC$13,'Toon Data'!J$2,FALSE)),"",VLOOKUP($A2,'Toon Data'!$A$3:$CC$13,'Toon Data'!J$2,FALSE)))

Is there a way to bring in the source font formatting with this vlookup formula? Cell A1 is a selection menu of skills, so the data changes depending on if the character has that skill.

 

[Yongle]

It is not posible to do that by formula
VBA can be used
see this recent thread (although OP @haitham1984 appears to have lost interest before getting to a solution)

vlookup - font color

Hello All, I am writing questions for a quiz and within each cell of that column (Questions Column), I am manually writing text with different font colors. The next 4 columns are the multiple choice answers for the question on the same row, which also can have different font colors. example...

www.mrexcel.com

 

[muhleebbin]

Thanks Yongle

I saw another thread about this and it seemed like the vba needed to be specific to each use case. I'll keep searching

 

[Yongle]

I'll keep searching

Searching further will confirm that this cannot be done by formula and that the required output can be achieved by VBA
- VBA requires method to find the source cells(s) and instruction to copy & paste value and format FROM source TO target cells(s)

it seemed like the vba needed to be specific to each use case

That is correct - each lookup is different

 

[muhleebbin]

Searching further will confirm that this cannot be done by formula and that the required output can be achieved by VBA

Sorry meant searching for a macro to assist in this

 

[Yongle]

Test as follows

Take a copy of the sheet containing the vlookup formula
Run macro from copied sheet
Check values in L2:R5

Let me know how you get on

Place code in a module like Module1

Option Explicit

Sub LookupValueAndFormat()
    Dim cel As Range, DataRng As Range, source As Range
    Set DataRng = Sheets("Toon Data").Range("A3:A13")
    Application.ScreenUpdating = False
    For Each cel In Range("L2:R5")
        cel.ClearContents
        Set source = GetCellToCopy(cel, DataRng)
        If Not source Is Nothing Then
            source.Copy
            cel.PasteSpecial (xlPasteValues)
            cel.PasteSpecial (xlPasteFormats)
        End If
   Next cel
End Sub

Private Function GetCellToCopy(c As Range, look As Range) As Range
    Dim myRow As Long
    On Error Resume Next
    myRow = WorksheetFunction.Match(c.Offset(, 1 - c.Column), look, 0) + 2
    On Error GoTo 0
    Set GetCellToCopy = Nothing
    If myRow > 0 Then Set GetCellToCopy = look.Parent.Cells(myRow, c.Column - 2)
End Function