A couple of ideas, don't know if any of them would work in your case
=INDEX($A$1:$B$7,MATCH(TRIM(G1)&"*",$A$1:$A$7&"s", 0),2)
entered with ctrl + shift & enter
or you could create a list (if there aren't too many values in G) and use a
dropdown with datavalidation and let the users select from previously
entered words that will match, that way you don't have to worry about
part/parts etc
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"ExcelQuestion"
wrote in message
news:ExcelQuestion.278wyn_1146683409.1307@excelfor um-nospam.com...
Hi Peo,
I've tried both formulas and didn't get the desired result. I need a
formula that could be used throughout the rest of column G.
G1 could either be Development Costs or Development Cost. It would be
able to match $A$1:$A$7's data. (Your first formula does exactly
that).
However, I couldn't apply that command for G2 and downwards because of
the instance number within the Substitute command.
ie. G2 could be Utilities, it would match $A$1:$A$7 without trimming
that the "s". G3 could either be Part or Parts. It would still match
to $A$1:$A$7's "Parts".....which may or may not have a "s" at the end
also.
Basically, trying to find a workaround for singular and plural words.
Any ideas?
Thanks,
Ricky
Peo Sjoblom Wrote:
If that is the only difference you can use
=INDEX($A$1:$B$7,MATCH(SUBSTITUTE(TRIM(G1),"s","", 2),$A$1:$A$7,0),2)
or
=INDEX($A$1:$B$7,MATCH(LEFT(TRIM(G1),16),$A$1:$A$7 ,0),2)
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"ExcelQuestion"
wrote in message
news:ExcelQuestion.278tpy_1146679205.9445@excelfor um-nospam.com...
Hello,
Here's my formula for an index and match where cell G1 has the data
of
Development Cost.
=INDEX($A$1:$B$7,MATCH(TRIM(G1),$A$1:$A$7,0),2)
What's an improved formula for matching $A$1:$B$7 to include
Development Costs <<< with the "S" at the end? Sometimes, a person
omits or adds "S" at the end and this formula doesn't recognize it.
I don't want to trim off column G datas with "S" ending because some
words do require a "S" at the end ie. Utilities.
Thanks in advance,
Ricky
--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile:
http://www.excelforum.com/member.php...o&userid=34059
View this thread:
http://www.excelforum.com/showthread...hreadid=538567
--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile:
http://www.excelforum.com/member.php...o&userid=34059
View this thread: http://www.excelforum.com/showthread...hreadid=538567