Search code examples
excelexcel-formulaexcel-match

Using formatted text as match function lookup value

My look up array is of format DI-0001. First 3 places are fixed as “DI-“ and next 4 are any number but of fixed length of 4. My look up array is a simple number which is less than 10000. Hence always equals to less than 4 digits. I formatted my lookup value column as “DI-“0000 so as to match the lookup array. But now my match function is breaking down, giving #N/A error. I understand the error, but don’t know how to overcome it.

Solution

  • You are not looking for a number in your look up array so you have to convert the value you are looking up to a string as well, not just format it as string.

     =VLOOKUP("DI-"&TEXT(value,"0000"),array,2,0)

    Or preferably,

     =INDEX(B1:B9,MATCH("DI-"&TEXT(F1,"0000"),A1:A9,0))

    enter image description here