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