Excel Guides

Maintaining Text Formatting in a Lookup in Excel

When you lookup data in Excel, it is important to maintain the formatting of the text. Otherwise, the data may not be displayed correctly. There are a few ways to do this:

  1. Use the Format as Table option.
  2. Use the Conditional Formatting feature.
  3. Use a macro to format the data.

1. Use the Format as Table Option

This option is available in Excel 2007 and later versions. To use it, select the data that you want to lookup, then click the Insert tab on the ribbon and click Table. In the Create Table dialog box, make sure that your data has headers and that the My table has headers checkbox is selected, then click OK. Your data will now be formatted as a table.

Learn more about using the Format as Table option >>

Learn more about using tables for lookup in Excel >>

Home tab on the ribbon and click Conditional Formatting >> . In the conditional formatting dialog box, select one of the options under "Highlight Cell Rules". For example, if you want to highlight cells that contain text values, select "Text that Contains". Then enter the value or text that you want to find in the "Format only cells with:" field and click "OK". The cells that meet your criteria will now be highlighted.

3. Use a Macro to Format Data
Macros can be used to automate tasks in Excel.
For example, you could create a macro that would format your data as a table.
To do this, select your data and press Alt+F8 to open the Macro dialog box.
In the Macro dialog box, enter a name for your macro and click "Create".
In the Visual Basic Editor (VBE), enter the following code:
Sub FormatAsTable() 'Selects all of the cells in th e active sheet ; ; ;Cells . Select ; ; ;'Formats th e selection as a table with headers ; ; ;ActiveSheet . ListObjects . Add(xlSrcRange , Selection , , xlYes). Name = "MyTable" ; ;End Sub ; ; < p >Click "Run" to run your macro.< / p > Your data will now be formatted as a table with headers.< br / > 4 . Use Lookup Functions in Excel< br / > There are several lookup functions available in Excel : VLOOKUP , HLOOKUP , MATCH , INDEX , OFFSET . These functions can be used together to lookup data in Excel . For example , if you have a list of names and addresses in two columns , you could use VLOOKUP to lookup each name and return its corresponding address . Or , if you have a list of sales figures for each month , you could use HLOOKUP to lookup each month and return its corresponding sales figure . Learn more about using lookup functions in Excel >>

Move beyond 


Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.