errors in vlookup

March 2, 2010 at 00:46:50
Specs: Windows Vista
I need to pull information from one workbook into another. I am using vlookup to do this. The problem is vlookup only works if the values I am comparing are an exact match. In my case I am comparing book titles. In some cases the titles may not match because one title may have the year at the end of it in one workbook and it doesn't on the other work book. For example: The Great Gatsy is listed in workbook while on the other it is listed as Great Gatsby, The (2008). Is there a way to pull the value by doing an approximate match of some kind? Or by matching the first several characters? Please help my title lists are very long and doing this manually takes too much time. Thanks

See More: errors in vlookup

Report •

March 2, 2010 at 11:24:00

Here is a formula that you could use to create a new column of names and which removes some of the variability. It is a long way from a perfect solution, but it should reduce the number of mismatches.

Using your Great Gatsby examples it returns "Great Gatsby" for three variations of the name as follows:

	A				B
2	The Great Gatsby		Great Gatsby
3	Great Gatsby			Great Gatsby
4	Great Gatsby , The (2008)	Great Gatsby 

The original listing is in column A, starting in cell A2
In cell B2 enter this formula:

=IF(ISERROR(FIND(",",IF(LEFT(A2,4)="The ",RIGHT(A2,LEN(A2)-4),A2))),
IF(LEFT(A2,4)="The ",RIGHT(A2,LEN(A2)-4),A2),LEFT(IF(LEFT(A2,4)="The ",
RIGHT(A2,LEN(A2)-4),A2),FIND(",",IF(LEFT(A2,4)="The ",

The formula has been split onto 4 lines for ease of viewing, so it will have to be reassembled to make a single formula.

Then drag the formula down as many rows as required.

I have only tested it on this minimal sample, so I can't tell how it will handle all the various combinations of names in you list.

If there are extra leading or trailing spaces with some entries, you could wrap the whole of the above formula in TRIM(), to remove them. You might try doing the same for the titles you are looking up.

Regarding adding TRIM() to the formula, if you are using Excel 2003, you can't use TRIM() directly as you exceed the levels of nesting allowed, so in cell C2 use this formula =TRIM(B2) and do your VLOOKUP() on column C instead. Although I haven't tried it, you should be able to add TRIM() as part of the formula in cell B2 when using Excel 2007.

Hope that this is of interest to you.


Report •
Related Solutions

Ask Question