replicate index match in VBA to maintain individual font formats within a cell.

[brucecanny]

First let me acknowledge that each component of my question has been covered however I think this is unique, and probably simpler as I only want to return the value AND format of one cell, but it is from an closed external file. I'm new to VBA and the answers to each component of my question all seem to be very compicated in some way (array's, copying entire sheets or columns...). The INDEX/MATCH works fine however the source fonts have different colors within the same cell and INDEX/MATCH only returns the Value.. I hope this is enough information. Here is my Index Match.

=INDEX('[Race-9-Ball-Balls.xlsx]Race'!$B$2:$Q$17,MATCH(N8,'[Race-9-Ball-Balls.xlsx]Race'!$A$2:$A$17,1),MATCH(N9,'[Race-9-Ball-Balls.xlsx]Race'!$B$1:$Q$1,1))

This is what it returns:

❺❼

<tbody>
</tbody>

But I want: ❺❼ <tbody> </tbody>

<tbody>
</tbody>

 

[brucecanny]

I realized I should have provided more detail. This is a pool tournament spreadsheet and the "Race" is shaded green. You can see the un-formatted output in the screenshot along with a screenshot of the source file.

Source:

Handicap	11	12	13	Pro	SmP
1	❻	❺	❺❼	❹❼	❺❼
2	❻	❺	❺❼	❹❼	❺❼
3	❻	❺	❺❼	❹❼	❺❼

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Result:

	M	N	O
7	❹❼
8	3	1	Nok
9	10	Pro	Nikeel

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Desired result:

	M	N	O
7	❹❼
8	3	1	Nok
9	10	Pro	Nikeel

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>