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