Excel 2007 : Match a name on one worksheet with a name on another work sheet

[koolhelpdeskdude]

Hi

Here is the problem.

One worksheet contains the historical data of the names of the companies. On another work sheet is data of the companies that have delisted. The matching should be case insensitive

I have attached the spreadsheet. The companies are on the worksheet List of All Companies and the matching worksheet is Delisted. Basically I trying to find out which have been delisted.

Many thanks.

[koolhelpdeskdude]

Sorry here's the file

[koolhelpdeskdude]

Sorry here's the file

[Jbentley]

Hi,

Using a Vlookup formula with the 'range lookup' operator set to TRUE will return the nearest match within the given range.
(when set to FALSE the 'range lookup' operator will force the formula to find and exact match.

eg.

Please Login or Register  to view this content.

C9 being the reference to the company name in your 'List of Companies...' sheet


Hope that helps..

[koolhelpdeskdude]

Dear Bentley,

Thanks for your advice. But in the full database, I have lots of names that are quite similar. Is there a way to match the names that is not case-sensitive.

Also I would like to return the Column 1 in the Delisted worksheet.

Many thanks

[JBeaucaire]

Try this in D8 and copy down. It will answer TRUE if the item is NOT on the DELISTED sheet, it will answer FALSE if it IS listed. These seem to be the appropriate answers for a column called "Listed".

=ISERROR(MATCH(TRIM(C8),Delisted!A:A,0))


MATCH() is not case-sensitive, but your sample data has a LOT of extra spaces in column C, so the TRIM() function helps with that.

[koolhelpdeskdude]

It works! Ohhh Thank you, thank you ohhh you rocket scientist!

One more question if I may. is there a way for me to see step by step how the formula works? I want to understand the formula better.

Kind regards

[JBeaucaire]

My favorite thing in the Excel has become the Formula Evaluator. Turn on the Formula Auditing toolbar. Click a cell. Click on Evaluate Formula and step through the formula by clicking Evaluate button.