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