Find the name in a range and return a value.

[sungen99]

I am tying to create a formula that will search the entire string in A2 to find the color found in D24:D60 and then rewrite the string to be like B2 in this example.

Note that B2s vernacular is replacing Air Blue.jpg with the looked up code CB but in each case its adding the word TOM to the front of it, finishing up with .jpg

Thank you

[Richard Buttrey]

You didn't show what formula you were using. However 'Tom' can only come from D18 so it's not clear what D18 is doing.

Try

Please Login or Register  to view this content.

[sungen99]

I would move the Name to call it and TOM, D18 into a another place if you want. that will be a constant.. so you enter a make and it will use that to put in front of it all. D18 is a manual enter.

D

[hemesh]

in B2 copy paste below and drag down

Please Login or Register  to view this content.

[tim201110]

=substitute(a2,lookup(1000,find($d$24:$d$60,a2),$d$24:$d$60),lookup(1000,find($d$24:$d$60,a2),$e$24:$e$60))

[sungen99]

Also, the number of "\" will not always be the same. i was wondering if you could just use the "\Air Blue.jpg" portion to do the look up for? switch it up to be "\TOM-CB.jpg" and replace it in the full string?

That was what i was originally trying to do with no luck.

[hemesh]

in that case a little change to Tim's formula

Please Login or Register  to view this content.

[sungen99]

YEP!!! this works.. thanks all!!!!

[hemesh]

here is another one

Please Login or Register  to view this content.

may be the the formula from POST#7 can get you undesired results such that in B13 it may give end string as ""TOM-CA Cocoa.Jpg" and so on.
I could see there are exact matches in your given column. more over this will work for more than 5 "\"