Match Index #N/A

[UKSteveM]

Hello All,

I have a problem with the following formula;

=MATCH($B66&O$3,PNumber&OutputDates1,0)

I am entering this to find a match on named ranges (PNumber and OutputDates1). My two criteria are listed on the sheet in B66 and O3. When i enter the formula (as an array formula) i get an '#N/A' result.

The concatenated query is 'P5378A40787' when i step through the calculation steps to try and discover the issue, i can see the match in the array (PNumber&OutputDates1), however this is not returned as a match with the array location which i would expect.

Any ideas why i get the error response?

Best Regards,

Steve

 

[delaneyjm]

Are there any leading or trailing spaces in the lookup array?

 

[UKSteveM]

No, no spaces, i have tried copy and pasting the text and running the match function directly on a separate sheet and get the same result.

I've also copied the text directly into the match function so it is exactly the same.

I can't upload a sample to review unfortunately.

 

[delaneyjm]

Take a look at Excel Jeanie. That will let you post a screenshot of your sheet that is compatible with the Board.

 

[Aladin Akyurek]

Hello All,

I have a problem with the following formula;

=MATCH($B66&O$3,PNumber&OutputDates1,0)

I am entering this to find a match on named ranges (PNumber and OutputDates1). My two criteria are listed on the sheet in B66 and O3. When i enter the formula (as an array formula) i get an '#N/A' result.

The concatenated query is 'P5378A40787' when i step through the calculation steps to try and discover the issue, i can see the match in the array (PNumber&OutputDates1), however this is not returned as a match with the array location which i would expect.

Any ideas why i get the error response?

Best Regards,

Steve

Better...

=MATCH($B66&"|"&O$3,PNumber&"|"&OutputDates1,0)

=MATCH(1,IF(PNumber=$B66,IF(OutputDates1=O$3,1)),0)

Two possible issues regarding the unexpected #N/A results...

(a) Leading/trailing or stray spaces;

(b) Look up value is text vs the match range is numeric or vice versa.

 

[UKSteveM]

Unfortunatly i cannot install excel jenie on this work machine, i'll try from home tonight.

I think i may have found some reason behind the problem.

The OutputDates1 array is 25 rows and 10 columns, if i define the whole range as the array i get the #N/A error, if i reduce the range to one column i get the result. It appears the search is only being done on the first column of the array. Is this correct?

If so, is there a method to search a multi column array?

 

[UKSteveM]

I've managed to install Excel Jeanie and produce the HTML, not sure how to post it, but the code is below;

[RANGE=cls:xl2bb-100][XR][XH=cs:14]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][XH]M[/XH][/XR][XR][XH]1[/XH][XD=h:l|cls:fx][FORMULA={=MATCH("P5378A40756",D1:M14,0)}]#N/A[/FORMULA][/XD][XD][/XD][XD][/XD][XD=h:l]P5374[/XD][XD=h:l]P5374[/XD][XD=h:l]P537440725[/XD][XD=h:l]P5374[/XD][XD=h:l]P537440756[/XD][XD=h:l]P5374[/XD][XD=h:l]P537440787[/XD][XD=h:l]P5374[/XD][XD=h:l]P5374[/XD][XD=h:l]P537440817[/XD][/XR][XR][XH]2[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5378[/XD][XD=h:l]P5378[/XD][XD=h:l]P537840787[/XD][XD=h:l]P537840817[/XD][XD=h:l]P537840848[/XD][XD=h:l]P537840878[/XD][XD=h:l]P537840909[/XD][XD=h:l]P537840940[/XD][XD=h:l]P537840969[/XD][XD=h:l]P537841000[/XD][/XR][XR][XH]3[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5378A[/XD][XD=h:l]P5378A[/XD][XD=h:l|bc:ffff00]P5378A40756[/XD][XD=h:l]P5378A[/XD][XD=h:l]P5378A40787[/XD][XD=h:l]P5378A[/XD][XD=h:l]P5378A40817[/XD][XD=h:l]P5378A[/XD][XD=h:l]P5378A40848[/XD][XD=h:l]P5378A40878[/XD][/XR][XR][XH]4[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5524[/XD][XD=h:l]P5524[/XD][XD=h:l]P552440787[/XD][XD=h:l]P5524[/XD][XD=h:l]P552440817[/XD][XD=h:l]P5524[/XD][XD=h:l]P552440848[/XD][XD=h:l]P5524[/XD][XD=h:l]P552440878[/XD][XD=h:l]P552440909[/XD][/XR][XR][XH]5[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5611[/XD][XD=h:l]P5611[/XD][XD=h:l]P561140756[/XD][XD=h:l]P5611[/XD][XD=h:l]P561140787[/XD][XD=h:l]P5611[/XD][XD=h:l]P561140817[/XD][XD=h:l]P5611[/XD][XD=h:l]P5611[/XD][XD=h:l]P561140848[/XD][/XR][XR][XH]6[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5715[/XD][XD=h:l]P5715[/XD][XD=h:l]P571540756[/XD][XD=h:l]P5715[/XD][XD=h:l]P571540787[/XD][XD=h:l]P5715[/XD][XD=h:l]P571540817[/XD][XD=h:l]P5715[/XD][XD=h:l]P571540848[/XD][XD=h:l]P571540878[/XD][/XR][XR][XH]7[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5740[/XD][XD=h:l]P5740[/XD][XD=h:l]P574040787[/XD][XD=h:l]P5740[/XD][XD=h:l]P574040817[/XD][XD=h:l]P5740[/XD][XD=h:l]P574040848[/XD][XD=h:l]P5740[/XD][XD=h:l]P5740[/XD][XD=h:l]P574040878[/XD][/XR][XR][XH]8[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A40848[/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A40878[/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A40909[/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A[/XD][XD=h:l]P5749A40940[/XD][/XR][XR][XH]9[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B40848[/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B40878[/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B40909[/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B[/XD][XD=h:l]P5749B40940[/XD][/XR][XR][XH]10[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5769[/XD][XD=h:l]P5769[/XD][XD=h:l]P576940848[/XD][XD=h:l]P5769[/XD][XD=h:l]P576940878[/XD][XD=h:l]P5769[/XD][XD=h:l]P576940909[/XD][XD=h:l]P5769[/XD][XD=h:l]P5769[/XD][XD=h:l]P576940940[/XD][/XR][XR][XH]11[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5811[/XD][XD=h:l]P5811[/XD][XD=h:l]P581140756[/XD][XD=h:l]P5811[/XD][XD=h:l]P581140787[/XD][XD=h:l]P5811[/XD][XD=h:l]P581140817[/XD][XD=h:l]P5811[/XD][XD=h:l]P5811[/XD][XD=h:l]P581140848[/XD][/XR][XR][XH]12[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5812[/XD][XD=h:l]P5812[/XD][XD=h:l]P581240909[/XD][XD=h:l]P5812[/XD][XD=h:l]P581240940[/XD][XD=h:l]P5812[/XD][XD=h:l]P581240969[/XD][XD=h:l]P5812[/XD][XD=h:l]P5812[/XD][XD=h:l]P581241000[/XD][/XR][XR][XH]13[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][XD=h:l]P5020[/XD][/XR][XR][XH]14[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:l]P5200[/XD][XD=h:l]P5200[/XD][XD=h:l]P520040848[/XD][XD=h:l]P5200[/XD][XD=h:l]P520040878[/XD][XD=h:l]P5200[/XD][XD=h:l]P520040909[/XD][XD=h:l]P5200[/XD][XD=h:l]P5200[/XD][XD=h:l]P520040940[/XD][/XR][XR][XH=cs:14][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Aladin Akyurek]

Unfortunatly i cannot install excel jenie on this work machine, i'll try from home tonight.

I think i may have found some reason behind the problem.

The OutputDates1 array is 25 rows and 10 columns, if i define the whole range as the array i get the #N/A error, if i reduce the range to one column i get the result. It appears the search is only being done on the first column of the array. Is this correct?

Yes, that's correct. MATCH cannot work multicolumn/multirow data areas.

If so, is there a method to search a multi column array?

Control+shift+enter, not just enter:

=MIN(IF(PNumber=$B66,IF(OutputDates1=O$3,ROW(PNumber)-ROW(INDEX(PNumber,1,1))+1)))

This yields within area row-number...