In this Excel tutorial you learn how to Use Vlookup and Keep Formatting Consistent in Excel.
When you use the VLOOKUP function in Excel, the formatting of the lookup value or the lookup result may change depending on the format of the source data. However, there are a few ways to keep the formatting consistent:
Use the "Exact" match option
When using the VLOOKUP function, you can specify an optional "match_type" argument. If you set this argument to "0" or "FALSE", Excel will perform an exact match, which means that the formatting of the lookup value will not affect the result.
Use the TEXT function
If you want to preserve the formatting of the lookup value, you can use the TEXT function to format the value as text before using it in the VLOOKUP formula. For example, you can use the formula =VLOOKUP(TEXT(A1, "0.00"), Table1, 2, FALSE) to lookup a value in Table1 based on a number in cell A1 formatted as a decimal with two places.
Use conditional formatting
After performing the VLOOKUP, you can use conditional formatting to apply the same formatting to the lookup result as the source data. To do this, select the cell with the lookup result, go to the "Home" tab in the Excel ribbon, and click on "Conditional Formatting". Then, select "Highlight Cells Rules" > "Equal To" and enter the source data value. Finally, choose the desired formatting for the matched cells.
By using these methods, you can ensure that the formatting of the lookup value or the lookup result remains consistent, even if the source data has a different format.