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
If that is the only difference you can use
=INDEX($A$1:$B$7,MATCH(SUBSTITUTE(TRIM(G1),"s","",2),$A$1:$A$7,0),2)
or
=INDEX($A$1:$B$7,MATCH(LEFT(TRIM(G1),16),$A$1:$A$7,0),2)
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"ExcelQuestion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
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
Originally Posted by 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
news:[email protected]...
>
> 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
>
>
> Peo Sjoblom Wrote:
>> If that is the only difference you can use
>>
>> =INDEX($A$1:$B$7,MATCH(SUBSTITUTE(TRIM(G1),"s","",2),$A$1:$A$7,0),2)
>>
>>
>> or
>>
>> =INDEX($A$1:$B$7,MATCH(LEFT(TRIM(G1),16),$A$1:$A$7,0),2)
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>> "ExcelQuestion"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > 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
>
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
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 A2 Development, then A3 Develop. In G1, I entered Develop and it picked up the first "Develop"ment Cost instead of the "Develop" in A3.
It was just a quick idea not really tested, it's hard if you have 100
different words
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"ExcelQuestion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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 A2
> Development, then A3 Develop. In G1, I entered Develop and it picked
> up the first "Develop"ment Cost instead of the "Develop" in A3.
>
>
>
> ExcelQuestion Wrote:
>> 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
>
>
> --
> ExcelQuestion
> ------------------------------------------------------------------------
> ExcelQuestion's Profile:
> http://www.excelforum.com/member.php...o&userid=34059
> View this thread: http://www.excelforum.com/showthread...hreadid=538567
>
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
>
>
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
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)
"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
>
>
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
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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks