Finding exact text in 2 worksheets

Microsoft Excel 2007
March 2, 2010 at 13:40:28
Specs: Windows XP
I have data in 2 worksheets, one a large universe of possible text (in this case auto parts numbers, which are alphanumeric)& the other worksheet a small subset of the first. For each part number in the smaller worksheet, I want to search the larger worksheet to see if the text is there and if yes then let me know. Sumif doesn't work, and I can't get Index/Match to work either. Any ideas?


See More: Finding exact text in 2 worksheets

Report •

March 2, 2010 at 15:56:20



For Lookup_value use a reference to a cell in the smaller list.
table_array is the main parts list to be searched - it needs to be a single column.

As you only want to know if the name is present in the main list, then
col_index_number =1,
VLOOKUP() will return the value from the only column in your table array.

range_lookup is the value FALSE (note this is an Excel logical value - not text - so do not put it in quotes). FALSE ensures an exact match for your search.

If there is no exact match, VLOOKUP() returns the #NA error value.

To get a better result for items with no match do something like this:
=IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$A$2:$A$6000,1,FALSE)),"No Match","Found")

Without information on where you have your data I can't be more specific. If you need more advice, please include more specific details, such as worksheet names, ranges for data and information on how the data is organized.


Report •

March 3, 2010 at 08:28:00
Thanks for the response. Unfortunately, it didn't work (everything csame up #na), maybe because I'm trying to look up text? Also not sure in which worksheet the formula should be. Some added details.

The main sheet is called Parts. It has about 5000 rows of part numbers. The numbers are alpha-numeric, though I could make them numeric-alpha if that helps, in general format (I tried using copy and paste special values but that didn't work either). That smaller sheet is called Sheet1, and contains about 200 part numbers. Each part # in the smaller is contained in the main sheet. By tagging such numbers I can find the numbers in the main sheet that are not in the smaller sheet.

Hope this clarifies, and thanks again.

Report •

March 3, 2010 at 10:54:21

You haven't said what cells your data is in, so for this example I have created a short parts list on a Worksheet named "Parts", in Column A, with A1 containing a header.
the parts list is alpha-numeric and I included values xy-123 to xy-128 and yz-456 to yz-460 in cells A2 to A12

On a worksheet named "Sheet1" I placed a short list of items to be found, again starting in Cell A2.

In column B, I put this formula in cell B2:

=IF(ISNA(VLOOKUP(A2,Parts!$A$2:$A$12,1,FALSE)),"Not found","Found")

then dragged the formula down a further two rows.

This is what I got:

	A	B
1	Item	Status
2	xy-128	Found
3	yz-456	Found
4	ab-100	Not found

Your NA results were not due to searching for text - VLOOKUP() finds text just fine - it was more likely that one or other part of the VLOOKUP() references was pointing to the wrong place.

If the parts list also contained a description of each item in column B, then this formula on Sheet1 would either return "Not found" or the description of the part.

=IF(ISNA(VLOOKUP(A2,Parts!$A$2:$A$12,1,FALSE)),"Not found",

The formula has been split on to two lines for ease of viewing.
Note that instead of returning "Found" it runs VLOOKUP() again but this time returning a value from the second column of the table, with table range now referring to 2 columns (Parts!$A$2:$B$12).
	A	B
1	Item	Description
2	xy-128	bolt cap head M6 2 cm s/steel
3	yz-456	hex nut M6 s/steel
4	ab-100	Not found

If you still have difficulties, please include actual cell references for your lists and a copy of the formula used, including which cell you have copied it from (copy the formula from the formula bar)


Report •

Related Solutions

March 4, 2010 at 09:54:51
Than you again for your response - it works but not with my data! But it got me to thinking about why and now I can make the formula work but have another question.

The alpha-numeric data (part numbers) in the 2 worksheets are from 2 different sources. They are formatted exactly the same but yet Excel sees them differently so it never gives me a match. If I go to the smaller worksheet (Sheet1) and edit the part number to delete a space that for some reason appears at the end of the string the formula works perfectly just as you proposed. Obviously I can't do this manually for all the parts. The numbers have different lengths so I can't use text to columns and just eliminate the extra space. The data in the 2 sheets are formatted the same; I've tried copy paste special, tried adding a space at the end of part numbers in the min worksheet (sheet "Parts") but that didn't work either. Same with Left and Right functions since the numbers have different lengths and I can't apply the formula to all part numbers.

Any other suggestions? It's frustrating because it's so close to being solved.

Thanks again.

Report •

March 4, 2010 at 10:16:26
Look at the TRIM and CLEAN functions.

For example, this will strip off a trailing space before it does the VLOOKUP.


Report •

March 4, 2010 at 13:39:27
Fantastic! I had tried Clean but didn't know about Trim.

Thanks again.

Report •

Ask Question