Product Manager

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.

By: Mark

Leave a Reply

Your email address will not be published. Required fields are marked *