Index and match function use

Copper Contributor

Appreciate if someone could help me understand the error.

Index and Match functions are used for getting the data from the table 'A' as per the attached excel file in the link below.

Trying to get the cell B7 updated based on value generated in F5.!AvqHVc_GIXwehkFX1Ty1XyC2mZU-?e=CWsjQ9 

3 Replies


The result of the concatenation formula in F5 is a text value, but B11:B23 contain numbers. That won't match.

To correct this, change the formula in F5 to return a number: =--(B4&C4&D4&E4)

See the attached workbook.

Thanks for your response. It works fine when B4, C4, D4, E4 is equal to 1, how can if B4 & C4 are equal to 0. In such a case, not able to get the code correctly. Appreciate any solution for this issue as well.


That is because your data are inconsistent: some are text, others are numbers:


In the attached version, ALL item codes have been converted to text. The formula is now the original one,