Search code examples
excelindexingnullexcel-indirect

Excel not recognizing values and outputting "0"

I am using the below formula in excel. It works for half the data and returns "0" for the other half. Some of the cells that I reference in the formula are joint strings and numbers (2 cells combined).

=INDEX(INDIRECT("Prices!"&K2&":"&K2),MATCH(H2,Prices!$C:$C),0)

I have carried out the following checks but to no avail, I am baffled!

  • Checked for empty space
  • Highlight the cells and did the Data>Text to columns
  • Changed the format to text
  • Tried putting an apostrophe at the beginning of the referenced cells.

I had no luck with any of these, any help would be much appreciated!!

Solution

  • If you are looking at the exact match, update your match formula to include 0.

    =MATCH(H2,Prices!$C:$C,0)