Match with #N/A error

%3CLINGO-SUB%20id%3D%22lingo-sub-1430956%22%20slang%3D%22en-US%22%3EMatch%20with%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430956%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENew%20here.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20one%20column%20I%20have%20a%20drop-down%20menu%20with%20specific%20texts.%20The%20second%20column%20rates%20each%20text%20(e.g.%2C%20MOR%20%3D%201.%20The%20third%20column%20has%20the%20texts%20written%2C%20one%20in%20each%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20cell%20that%20collects%20the%20data%20reads%20as%20such%3A%26nbsp%3B%3DMATCH(T5%2CR5%3AR14%2CMATCH(T6%2CR5%3AR14%2CMATCH(T7%2CR5%3AR14%2CMATCH(T8%2CR5%3AR14%2CMATCH(T9%2CR5%3AR14%2CMATCH(T10%2CR5%3AR14))))))%20and%20then%20I%20get%20the%20%23N%2FA.%20What%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1430956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hello,

 

New here. 

 

In one column I have a drop-down menu with specific texts. The second column rates each text (e.g., MOR = 1. The third column has the texts written, one in each cell.

 

My cell that collects the data reads as such: =MATCH(T5,R5:R14,MATCH(T6,R5:R14,MATCH(T7,R5:R14,MATCH(T8,R5:R14,MATCH(T9,R5:R14,MATCH(T10,R5:R14)))))) and then I get the #N/A. What am I doing wrong?

1 Reply
Highlighted

@Galtin  Hi and welcome.

I don't understand what you are trying to do.

As for the problem, the MATCH function has 3 arguments: lookup_value, lookup_array, and match_type

so in your formula the first MATCH has: MATCH(T5,R5:R14,...

So Excel will try to find the value in cell T5 in the range R5:R14

BUT then you have all the rest of that formula in the 3rd argument which is incorrect.

The 3rd argument can ONLY be -1, 0, or 1.

That 3rd argument indicates if the list is in order (small to large or large to small) and you should find the closest match or '0' meaning you only want an exact match and the list order doesn't matter.  I can't imagine that your cascade of MATCH functions are spitting out -1, 0 and 1s and hence you have an error.