I am on Excel 2016.
I am building a pricing tool where a combination of text and a number/text make up a section of the bill-of-material to look up costs. Based on the options that are selected from a dropdown list, I am using CONCATENATE (=CONCATENATE($F$14,$W$14) to build the component. For example, I have voltage (20) and a photocontrol (PE) and as a result, I can have 2 option, 20PE or 20 (a PE is not always required).
Voltage (20) is in cell F14
Voltage can be: AS, AH, 12, 20, 24, 34, 48
Photocontrol (PE) is in cell W14
Control can be: PE or nothing
The CONCATENATE is in cell D26.
I then use vlookup (=VLOOKUP($D26,SeriesTVC,2,FALSE), to determine if it is configurable or not configurable and to look up the cost.
This works for 20PE.
However, for 20, I get #NA.
If it is just any of the numbers, I get #NA. If it is just AS or AH I do not get an error.
The length of the data built in the CONCATENATE cell is the same as the VLOOKUP cell.
All cells are set to General.