Thanks for your efforts JMB. As long as the data in column A are plural
than cell G1 could either be singular or plural and it would work. It
won't work if column A is all singular and G1 is plural = "Development
Costs", the formula would result in error because it's not finding an
exact match so it'll try to add another "s" at the end which still
won't find the match.

I know you've tried trimming the (right,1) "s" if it is not neccessary
but it's the part that doesn't work.

Thanks again,
Ricky

JMB Wrote:
If there is no exact match, check the input for an "s" at the end. Add
an
"s" if there is not one already present or remove the "s" if there is
one
present. Of course, I have not considered working with that have an
"es"
plural form, or the plural form of words that already end in "s".


=INDEX($A$1:$B$7,MATCH(IF(ISNA(MATCH(TRIM(G1),$A$1 :$A$7,0)),IF(RIGHT(TRIM(G1),1)="s",LEFT(TRIM(G1),L EN(TRIM(G1))-1),TRIM(G1)&"s"),TRIM(G1)),$A$1:$A$7,0),2)

"ExcelQuestion" wrote:


Hi JMB,
Thanks for this idea. I've ran a few tests and I noticed that as
long
as the data in range $A$1:$A$7 are plural (with the "s" ending) then
this formula works. But, if the data is singular and if I were to
enter a "s" ending word then I would still get N/A error. Because
my
data column is actually over 100 rows from an imported sheet, I
couldn't verify each line for the singular/plural format. Any way
to
make it so that even if column G ends in "s" and range A1:A7 is
singular, I would still yield a search result without an error?

Thanks again,
Ricky

JMB Wrote:
You could try to match the word as is and, if that fails, add an s
on to
the
end of it.


=INDEX($A$1:$B$7,MATCH(TRIM(G1)&IF(ISNA(MATCH(TRIM (G1),$A$1:$A$7,0)),"s",""),$A$1:$A$7,0),2)

"ExcelQuestion" wrote:


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




--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059
View this thread: http://www.excelforum.com/showthread...hreadid=538567