Match with or without "S" ending

E

ExcelQuestion

Hello,
Here's my formula for an index and match where cell G1 has the data o
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 includ
Development Costs <<< with the "S" at the end? Sometimes, a perso
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 som
words do require a "S" at the end ie. Utilities.

Thanks in advance,
Rick
 
E

ExcelQuestion

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
 
P

Peo Sjoblom

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 data>validation 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" <[email protected]>
wrote in message
 
E

ExcelQuestion

Hi Peo,
This formula is good. You've added the wildcard for the match. Are
there ways other than an array formula because i'm sure other users
will not know the ctrl+shift+enter keystrokes?

I'm importing data consisting of about 100 rows into another tab. This
formula will point to it. So data validation list would not be
practical for this workbook.

Any ideas for a wildcard search without an array formula?

Thanks for everything so far.

Ricky
 
E

ExcelQuestion

One more thing. I noticed that the wildcard search is a bit open ended
Within $A$1:$A$7, i tested it with A1 Development Cost, then A
Development, then A3 Develop. In G1, I entered Develop and it picke
up the first "Develop"ment Cost instead of the "Develop" in A3.
 
J

JMB

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)
 
E

ExcelQuestion

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
 
J

JMB

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),LEN(TRIM(G1))-1),TRIM(G1)&"s"),TRIM(G1)),$A$1:$A$7,0),2)
 
E

ExcelQuestion

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

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

Thanks again,
Ricky
If there is no exact match, check the input for an "s" at the end. Ad
an
"s" if there is not one already present or remove the "s" if there i
one
present. Of course, I have not considered working with that have a
"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),LEN(TRIM(G1))-1),TRIM(G1)&"s"),TRIM(G1)),$A$1:$A$7,0),2)
Click to expand...
 
H

Harlan Grove

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.
Click to expand...
....

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)
 
Top