Forum Discussion

Brass Contributor

May 23, 2022

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 th...

excel

Formulas and Functions

office 365

HansVogelaar

MVP

May 23, 2022

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

clh_1496

Brass Contributor

May 23, 2022

HansVogelaar

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?

HansVogelaar
MVP
May 23, 2022
clh_1496

Yes - either make the formula return a number, or change A2:A26 to text values.
- clh_1496
  Brass Contributor
  May 24, 2022
  HansVogelaar
  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
  - HansVogelaar
    MVP
    May 24, 2022
    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.