Index & Match within a Matrix

Contributor

Hi,

 

I'm trying to use the index & Match function to look up a number along the side and letter across the top and return the value where they meet. I've included a column and row to show where the cells line up to give reference to the formula. 

 

=INDEX(NDT!B2:W26,MATCH('Upper'!B50,NDT!A2:A26,0),MATCH('Upper'!Y3,NDT!B1:W2,0))

 

When I look at the error, it's struggling with Y3 and B50 but I don't understand why as it's successfully identifying them as "21" and "F" so unsure why it then can't find them in the column and rows?

 

clh_1496_0-1653303158021.pngclh_1496_1-1653303177261.png

clh_1496_3-1653303203538.png

 

 

CellsA1B1C1D1E1Etc                 
1ActivityABCDEFGHIJKLMNOPQRSTUV
221NoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNoNo
322NoNoNoNoNoNoNoNoNoNo NoNoYesNoNoYesNoNoNoNoNo
423NoNoNoNoNoNoNoNoNoNo NoNo NoNo NoNoNoNoNo
etc24NoNoNoNoNoNoYesNoNoNo NoNo NoNo NoNoYesNoNo
 25No NoNoNoNo NoNo  NoYes NoNo No  NoNo
 26NoYesNoYesNoNoYesNoNoNo NoYes NoNo NoNo NoNo
 27No NoYesNoNo NoNo  NoYes YesNo NoNo NoNo
 28  No No  NoNo  No  YesNo NoNo NoNo
 29  No Noyes NoNo  Yes  YesNo NoYes NoNo
 30  Yes  yes NoNo     YesNo Yes   No
 31  Yes  yes NoNo      No Yes  YesNo
 32  Yes    NoNo        Yes   No
 33  Yes    NoNo      Yes Yes   No
 34  Yes     No        Yes    
 35  Yes     No             
 36  Yes     Yes        Yes    
 37  Yes                   
 38  Yes                   
 39  Yes                   
5 Replies

@clh_1496 

It looks like 'Upper'!B50 contains the text string "21".

If NDT!A2:A26 contains the numbers 21, 22, etc., MATCH will return #N/A

@Hans Vogelaar 

The 21 is the result from another calculation made by a different formula- does this mean I need to ensure both are same format for this to work?

@clh_1496 

Yes - either make the formula return a number, or change A2:A26 to text values.

@Hans Vogelaar 

Unfortunately, I've tried to change all the text and numbers and neither is working. I have noticed that the sheet called NDT does not get put in apostrophes like 'Upper Liners'! does in the formula and the cells highlighted in the formula don't get the red/blue/green/purple box around it like on the Upper Liner sheet either

@clh_1496 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.