Hi. I am using Excel 2010 to work on a large table, which has the following.

SAP CODE (A) SAP Description (B) Component ID (C)
EDD0039-A-500-503 EDD0039 AIR COMPRESSOR EDD0039 Compressor

The data in Component ID is similar SAP Description but not identical (nor is it in the same case), as you can see, "AIR" is missing.

I want to lookup data in Component ID and match it with the corresponding SAP CODE (or description) and enter whatever that might be in a new Column D (I am working with a database that will not accept anything but the identical codes. Once I know which one corresponds, I can fix up the formatting). Obviously I can do this manually with filter etc, but it will be a pain.

I have tried VLOOKUP (=VLOOKUP(A2,A:G,5,TRUE) (this one has the lookup value in Column A, and sorted smallest to largest) and some other data in columns B-G.

If I enter this formula, it returns a value from the correct column, but NOT the correct item (i.e. it basically returned the items in the exact order from Column B, whereas Column C is not in the same order, and that is what I want to do, match what is in Col C with Col B, then return either the SAME value from Col B, or else the Code in Col A (doesn't matter which one).

If I change the Array to say, B:D etc, I just get a #REF.

Any help extremely welcome.