I have the following formula in my sheet for running a horizontal index-match, but am getting #NA value as a return. Where is my formula wrong?

={INDEX('Univar Historical Prices'!$D$4:$AZ$22,MATCH('Revised Univar Price List-Mar'!$B5&'Revised Univar Price List-Mar'!H$4,'Univar Historical Prices'!$D$4:$AZ$5&'Univar Historical Prices'!$D$5:$AZ$5,0),4,1)}

  • 1
    it's very difficult to understand your formula without knowing your data. Have a look here, it might help: superuser.com/questions/1189830/troubleshoot-formulas-in-excel. – Máté Juhász Mar 24 '17 at 17:23
  • 1
    Are you entering as an array formula, with CTRL+SHIFT+ENTER and not just ENTER? – BruceWayne Mar 24 '17 at 17:48
  • 1
    The Match is in the place of Rows it should be in the place of columns, what is the 4 at the end of your formula switch it with the Match – yass Mar 24 '17 at 18:20
  • 1
    Match work with single Row or Single Column not with$ D$4:$AZ$5 it should be D4:AZ4 not 5 – yass Mar 24 '17 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.