Match with or without "S" ending
ExcelQuestion wrote...
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.
....
If there could be stray spaces (TRIM) and misspellings, does that mean
cell G1 is a user entry? If so, why not use a data validation drop-down
list with source range A1:A7?
That said, JMB's formula in a different branch of this thread does
work. Or you could try the following shorter formula.
=INDEX($A$1:$B$7,LOOKUP(100000,MATCH(LEFT(TRIM(G1) ,LEN(TRIM(G1))-{0,1}),
$A$1:$A$7,0)),2)
|