| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with or without "S" ending
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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" wrote in message news:ExcelQuestion.278tpy_1146679205.9445@excelfor um-nospam.com... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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" wrote in message news:ExcelQuestion.278tpy_1146679205.9445@excelfor um-nospam.com... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 datavalidation 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" wrote in message news:ExcelQuestion.278wyn_1146683409.1307@excelfor um-nospam.com... 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" wrote in message news:ExcelQuestion.278tpy_1146679205.9445@excelfor um-nospam.com... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 Peo Sjoblom Wrote: 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 datavalidation 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" wrote in message news:ExcelQuestion.278wyn_1146683409.1307@excelfor um-nospam.com... 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" wrote in message news:ExcelQuestion.278tpy_1146679205.9445@excelfor um-nospam.com... 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 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=538567 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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" wrote in message news:ExcelQuestion.2791tn_1146689704.2842@excelfor um-nospam.com... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match with or without "S" ending
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 JMB Wrote: 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 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=538567 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Index | Excel Worksheet Functions | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Find a match that;s not exact | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Linear Mode
