Find exact match for text in column A within column B then..

[Badger@work]

I have a range of products (1000). Each has a product code comprising two alphanumeric and between 1 to 6 numeric as its describer string, eg NZ32 or FT359A. When the images were produced, the data was saved with a filename that included this code and also a description of the product, eg "NZ32 - small drilling tool.jpg".

I need to find a way of matching the product code to its requisite image, and where there is a match, return the image's filename to a separate column within excel so that I can see which products require images. I also want to use the result to CONCATENATE with a drive path so that I can quickly update my Web Page with the proper image file for each product.

I have imported the filenames as text already, I just need the formula to look at each row in column A, attempt to match the text within each cell in that column with the filename string column B and return the filename if there is a match to the same row in Column C, if there is no match, just move to the next product and return a "MISSING" remark.

Thanks for your help

 

[Aladin Akyurek]

Re: Find exact match for text in column A within column B th

Why not post 5 rows of data and desired results?

 

[eliW]

Re: Find exact match for text in column A within column B th

Hi and welcome to the board,

Enter the following formula in c1 and copy down to the last row:

=INDEX($B$1:$B$1000,MATCH(A1&"*",$B$1:$B$1000,0))

Correct for the exact size of the range.

You'll get #N/A for strings that have no match.

Eli

 

[eliW]

Re: Find exact match for text in column A within column B th

You may use also "*"&A1&"*" in the formula to allow match of your string within the strings in column B :

=INDEX($B$1:$B$1000,MATCH("*"&A1&"*",$B$1:$B$1000,0))


Eli

 

[Badger@work]

compare text in col a to col b then return full string to c

Thnaks for your help. I see that this will compare two columns and that is great...I think I am asking too much of the formulas within excel to find any match within the column AND THEN do this.

Look in A1 then compare with all in column B, if a match is found, RETURN the full contents of the cell with the string match to the Column C ON THE SAME ROW as the string that was matched from Column A.

Thanks again!

 

[msvec]

Re: Find exact match for text in column A within column B th

i'm not sure if i'm understanding exactly what's going on, but if i am on the right track, try this. this will look in column B for a match to A1 and return what's adjacent to col B in col C...if nothing is found, it returns "MISSING".

=IF(ISERROR(VLOOKUP(A1,B1:C1000,2,FALSE))=TRUE,"MISSING",VLOOKUP(A1,B1:C1000,2,FALSE))

 

[NateO]

Re: Find exact match for text in column A within column B th

Hmmm, seems to be some mutual confusion here. The following functions in Column C are variation of the 2nd response, note the difference in the return result in Column D, not sure how close your names can come to one another:

Book5
	A	B	C	D
1	NZ32	FT359A-BlahBlahBlah.jpg	C:\temp\NZ323-Bigdrillingtool.jpg	c:\temp\NZ32-smalldrillingtool.jpg
2	FT359A	NZ323-Bigdrillingtool.jpg	C:\temp\FT359A-BlahBlahBlah.jpg	c:\temp\FT359A-BlahBlahBlah.jpg
3	jdkfksdlj	owaeioiew-Test.jpg	C:\temp\jdkfksdlj-MoreBlah.jpg	c:\temp\jdkfksdlj-MoreBlah.jpg
4	owaeioiew	jdkfksdlj-MoreBlah.jpg	C:\temp\owaeioiew-Test.jpg	c:\temp\owaeioiew-Test.jpg
5	Dummy	jdsfkljdsffdskdjfl	MISSING	MISSING
6	NZ323	NZ32-smalldrillingtool.jpg	C:\temp\NZ323-Bigdrillingtool.jpg	c:\temp\NZ323-Bigdrillingtool.jpg
Sheet1

Does this help? Or am I not following...

 

[Badger@work]

Re: Find exact match for text in column A within column B th

Thanks for everyone's help. I'm so impressed with the response. Special thanks to NateO who cut through the confusing description of the problem and pulled out the magic formula.

Job Done!

Thanks again