Match data in one column with data in another column

[lalosan]

I have product references in column "A" described with 3 letters, a dash and 2 or 3 numbers after (ie.: ROB-52 or ROB-792, etc.). In column "C" I have, in a different order, the same references but do not have the dash, therefore I have a problem matching them (ie.: ROB 52 or ROB 792). Then in column "D" I have quantities assigned to the references in column "C". I need the formula to take each reference in column "A" look it up in the references in column "C" and place in column "B" the quantity that matches the reference in column "C". See example below. Thanks in advance for your help.

A B C D
Reference Match Reference Quantity
ROB-52 6,300 ROB 77 1,000
ROB-792 2,500 ROB 231 5,200
ROB-77 1,000 ROB 52 6,300
ROB-32 ROB 792 2,500
ROB-231 5,200 ROB 49 150

regards,

Lalosan

[Haseeb Avarakkan]

Hello Lalosan, welcome to the forum.

If col_A reference has a "-" and col_C reference has a space instead of "-", you can use SUBSTITUTE "-" to a space. So try this in B2 & copy down.

=IFERROR(VLOOKUP(SUBSTITUTE(A2,"-"," "),C:D,2,0),"")

[lalosan]

Haseeb A

Thank you so much for your response. Your solution was accurate and helpful.

Lalosan