MATCH returning #N/A

I'm having MATCH returning #N/A for some items in the list and not for
others. I can't figure out why.

Here's the formula:
=IF($E26="","",INDEX(F$49:F$125,MATCH($E26,ECMOMLI st,0)))

ECOMLIst has a list of items to choose from in cell E26.

I want to be able to populate the cell the formula is in with the
corresponding info in the matching item in the ECOMLIst. It works for some
items in the list and not for others. I've checked the cell formats and
didn't find any inconsistencies, but I'm not exactly sure what I should be
looking for.

The #N/A return indicates to me that it can't find the match info?

And the formulas in the list that it is having specific trouble with is these:

="BLDG: Seal Air Leaks and Weatherstrip Doors (~"&N54&" CF)"

and

BLDG: Additional Attic Insulation (~X,XXX SF)


Does it have something to do with the tilda " ~ "? And if so, why?

Thanks,

"JEB" wrote:

I'm having MATCH returning #N/A for some items in the list and not for
others. I can't figure out why.

Here's the formula:
=IF($E26="","",INDEX(F$49:F$125,MATCH($E26,ECMOMLI st,0)))

ECOMLIst has a list of items to choose from in cell E26.

I want to be able to populate the cell the formula is in with the
corresponding info in the matching item in the ECOMLIst. It works for some
items in the list and not for others. I've checked the cell formats and
didn't find any inconsistencies, but I'm not exactly sure what I should be
looking for.

The #N/A return indicates to me that it can't find the match info?

Could be.

=vlookup(), =match() (and edit|Find, edit|replace) all allow you to use
wildcards.

* represents any set of characters.
? represents any single character.
~ is the escape character that says you really want to look for the * or ?
character:

So you'd use ~* to look for an asterisk
~? to look for a question mark
~~ to look for a tilde

I'd try:

=IF($E26="","",INDEX(F$49:F$125,MATCH(substitute($ E26,"~","~~"),ECMOMLIst,0)))

In fact, if you're not sure what could be in the data, you may want to avoid any
future problem by using something like:

=if($e$26="","",index(f$49:f$125,
match(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($e26,"~","~ ~"),"?","~?"),"*","~*"),
ECMOMLIst,0)))

(Untested, watch for typos -- did I close all my ()'s???)

JEB wrote:

And the formulas in the list that it is having specific trouble with is these:

="BLDG: Seal Air Leaks and Weatherstrip Doors (~"&N54&" CF)"

and

BLDG: Additional Attic Insulation (~X,XXX SF)

Does it have something to do with the tilda " ~ "? And if so, why?

Thanks,

"JEB" wrote:

I'm having MATCH returning #N/A for some items in the list and not for
others. I can't figure out why.

Here's the formula:
=IF($E26="","",INDEX(F$49:F$125,MATCH($E26,ECMOMLI st,0)))

ECOMLIst has a list of items to choose from in cell E26.

I want to be able to populate the cell the formula is in with the
corresponding info in the matching item in the ECOMLIst. It works for some
items in the list and not for others. I've checked the cell formats and
didn't find any inconsistencies, but I'm not exactly sure what I should be
looking for.

The #N/A return indicates to me that it can't find the match info?

--

Dave Peterson

Try this modification of your formula:

=IF($E26="","",INDEX(F$49:F$125,MATCH(SUBSTITUTE($ E26,"~","~~"),ECMOMLIst,0)))

Hope this helps / Lars-Åke


On Tue, 1 Jun 2010 13:47:01 -0700, JEB
wrote:

And the formulas in the list that it is having specific trouble with is these:

="BLDG: Seal Air Leaks and Weatherstrip Doors (~"&N54&" CF)"

and

BLDG: Additional Attic Insulation (~X,XXX SF)


Does it have something to do with the tilda " ~ "? And if so, why?

Thanks,

"JEB" wrote:

I'm having MATCH returning #N/A for some items in the list and not for
others. I can't figure out why.

Here's the formula:
=IF($E26="","",INDEX(F$49:F$125,MATCH($E26,ECMOMLI st,0)))

ECOMLIst has a list of items to choose from in cell E26.

I want to be able to populate the cell the formula is in with the
corresponding info in the matching item in the ECOMLIst. It works for some
items in the list and not for others. I've checked the cell formats and
didn't find any inconsistencies, but I'm not exactly sure what I should be
looking for.

The #N/A return indicates to me that it can't find the match info?