Here you will be presented with popular and handy ways to avoid a sure crash of your lookup formula. You will learn how to keep your index column ready to be pulled by any lookup formula: VLOOKUP, INDEX and MATCH, SUMPRODUCT…
1) Delete duplicates. Remember that a lookup formula returns the first instance of your lookup_value. Your index column must not contain duplicates.
Here’s how to delete them…
- Remove Duplicates in Excel 2007 by going to: Design>Tools>Remove Duplicates.
- Remove Duplicates manually in Excel 2007 by going to: Home>Styles>Conditional formatting>Highlight Cells Rules>Duplicate values.
- Remove duplicates in Excel 2003 by going to: Data>Filter>Advanced Filter>Unique records only.
2) Delete leading, trailing and inter spaces. Use the handy TRIM function to do the job.
The practical approach is here…
- Insert a column to the right of the column that contain spaces (ad-hoc column).
- Write the formula TRIM and copy downward.
- Copy and paste values to the index column.
- Delete the ad-hoc column.
3) Check for wrong spelling. This is one of the most difficult issues to fix because only you know the correct spelling of names or items.
There are two methods I use that may help you…
- Sort alphabetically and scan for irregular patterns. The human eye is a great resource. You will be amazed when you pick extraneous patterns in thousands of rows.
- Create a copy of the table and delete duplicates from the index column, this way you will have for example “Susan W.” and “Susan Wilson” in the resulting list. It is very easy now to know which entries are the same but with different spelling.
4) Sort ascending when you are performing an approximate lookup. Specifically when you use VLOOKUP or INDEX and MATCH.
To have peace of mind in your analysis, employ time in massaging your index columns until they are ready, and you will have more time for your analysis.
I remember my attitude when writing a VLOOKUP as “something for nothing”. I want the data to magically be pulled at the frontend. I persist in this approach until I became aware of it.