Search for certain text and bring a value below that text

[parkkibum]

hi all.

i have two sheets. i like to compare value between sheets. sheet1 is my calculation is sheet2 is computer generated value and i need to compare the value if they match.

they will always be in same format(please see the attached)

i have attached the excel sheet with further explanation.


pretty much i like to find certain text from sheet2 and paste a value below that text in sheet2.

thank you in advanced!

[Ace_XL]

Try

=INDEX(Sheet2!$A$1:$K$12,SUMPRODUCT((Sheet2!$A$1:$K$12=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255)),255)))*(ROW(Sheet2!$A$1:$A$12)+1)),SUMPRODUCT((Sheet2!$A$1:$K$12=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255)),255)))*COLUMN(Sheet2!$A$1:$K$1)))

Won't work for all values though, since yo don't have exact matches

[llbean1]

I am attempting to use the substitute function as well in a different context...without any luck.

I have column A with various strings of text all containing a year (2005-2013).
Example: "DIY Software Deluxe with State 2013 Win"

I need to reduce the year in each sentence by 1.
Example: "DIY Software Deluxe with State 2012 Win"

This is the formula that I have so far but can't get to work:
SUBSTITUTE(A1,{2005,2006,2007,2008,2009,2010,2011,2012,2013},-1)

With "A1" containing the "DIY Software Deluxe with State 2013 Win".

Any advice would be greatly appreciated!

[Tony Valko]

Try starting your own thread.

It's against the forum rules to post your question in someone else's thread.