Contributor

# Index & Match within a Matrix

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?

 Cells A1 B1 C1 D1 E1 Etc 1 Activity A B C D E F G H I J K L M N O P Q R S T U V 2 21 No No No No No No No No No No No No No No No No No No No No No No 3 22 No No No No No No No No No No No No Yes No No Yes No No No No No 4 23 No No No No No No No No No No No No No No No No No No No etc 24 No No No No No No Yes No No No No No No No No No Yes No No 25 No No No No No No No No Yes No No No No No 26 No Yes No Yes No No Yes No No No No Yes No No No No No No 27 No No Yes No No No No No Yes Yes No No No No No 28 No No No No No Yes No No No No No 29 No No yes No No Yes Yes No No Yes No No 30 Yes yes No No Yes No Yes No 31 Yes yes No No No Yes Yes No 32 Yes No No Yes No 33 Yes No No Yes Yes No 34 Yes No Yes 35 Yes No 36 Yes Yes Yes 37 Yes 38 Yes 39 Yes
5 Replies

# Re: Index & Match within a Matrix

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

# Re: Index & Match within a Matrix

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?

# Re: Index & Match within a Matrix

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

# Re: Index & Match within a Matrix

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

# Re: Index & Match within a Matrix

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.