Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH returning #N/A
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH returning #N/A
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH returning #N/A
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up any match, returning data | Excel Worksheet Functions | |||
Vlookup Match BUT Returning #NA | Excel Discussion (Misc queries) | |||
Match returning #N/A | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
VLOOKUP returning LAST match | Excel Discussion (Misc queries) |