# Excel 2007: Retaining source cell formatting when using VLOOKUP



## J2g (Feb 10, 2009)

Hi,

I have data in a table, and am using VLOOKUP to search through the 1st column for a word, and return all data in that row in the cells following, an example of the formula I am using in each cell is:

=VLOOKUP(CELL containing search term,table$:table$,Column number,FALSE)

There is nothing wrong with the formula, but wanted to give some context.

My issue is that some of the rows original data is formatted as £0,000.00 and others 10,000 and others 0.00%

Using VLOOKUP brings in the value only, but i need to include the formatting. 

I have tried using conditional formatting by assigning an extra column value (1, 2 or 3 depending on the formatting of that row) and then setting rules which change the formatting of the cells depending on this value. BUT the issue is that the 3 rules are applied in order, so it works to a point, but the rules are not reset when the sheet recalculates (when submitting the search term from the list) so once it gets to the last rule, it sticks.

If anyone has an alternative to VLOOKUP that would keep the formatting or any advice on the conditional formatting and making the rules start again from 1 that would be great.


----------



## turbodante (Dec 19, 2008)

I can't think how to achieve this without resorting to the help of a bit of VBA code, to check the cells formating after a match is found.


----------



## J2g (Feb 10, 2009)

Thanks for your advice.

I am not against the use of VBA coding, however, I am using the excel sheet as a back-end to an Xcelsius file, and i dont know how the two technologies will conflict when using VBA.

As I am not particularly advanced in VB, do you have any recommendations as to the sort of script i would need to use?

Many thanks again.


----------



## turbodante (Dec 19, 2008)

J2g said:


> Thanks for your advice.
> 
> I am not against the use of VBA coding, however, I am using the excel sheet as a back-end to an Xcelsius file, and i dont know how the two technologies will conflict when using VBA.


I have no idea what Xcelsus is, but I'd image that as you're already doin a bit of data manipulation - then you could easily run the macro after you've done the VLOOKUP



> As I am not particularly advanced in VB, do you have any recommendations as to the sort of script i would need to use?
> 
> Many thanks again.


Not being particularly advanced is fine, as long as you know how to run a bit of code - there's plenty of folk on here who're pretty handy with playing around with this sort of thing.

Here's one way to perform what I think you want. 

```
Sub FormatMatcher()
Dim Ar, Er, i, x, y As Integer
Ar = Cells(1, "A").CurrentRegion.Rows.Count
Er = Cells(1, "E").CurrentRegion.Rows.Count
For y = Ar To 1 Step -1
For x = Ar To 1 Step -1
If Cells(x, 5) = Cells(y, 1) Then
Cells(y, 1 + 1).Copy
Cells(x, 5 + 1).PasteSpecial Paste:=xlPasteFormats
' If you want to do away with using VLOOKUP altogether, then
' you could use the following instead
'Cells(x, 5 + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If
Next
Next
End Sub
```
Works by comparing cells in A1:B5 as the master table; and E1:F5 as your 'lookup' table. To save you time, I've commented out an extra bit where you could do away with the lookup.

HTH. Welcome to the forum btw.


----------

