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
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.
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 >>