using IF function with data ranges

[agentbox]

If I have a product description in row B (which is validated against a list held in J2:J21), how can I write a formula that returns the corresponding product code in column A (validated in a list held in column K2:K21)?

In other words, if somebody selects from the product description dropdown in column B I want to make sure the correct product code associated with that description shows in column A.

Thanks

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe
=VLOOKUP(B2,J$2:K$21,2,0)

Hope this helps

M.

 

[agentbox]

Thank you so much Marcelo! That's exactly what I needed.

Do you know if there a way to stop column A displaying N/A when column B s empty? (Ideally column A would display nothing if column B is empty)

 

[Marcelo Branco]

To avoid #N/A when B2 is blank

=IF(B2="","",VLOOKUP(B2,J$2:K$21,2,0))

M.

 

[agentbox]

Thank you very much Marcelo.

Happy New Year!