First column of range in VLOOKUP contains formula

[Pluggie]

I have two sheets.

Sheet 1 has two columns:
1. Code_Descr (example value: "12345_cookies")
2. Attribute_ID (empty)

Sheet 2 has 4 columns:
1. Code (example value: 12345)
2. Descr (example value: "Nr-cookies")
3. Code_Descr (formula: =A2&"_"&B2)
4. Attribute_ID (example value: "BHU340098")

The idea is to fill the column Attribute_ID in sheet 1 with the values from
sheet 2. But there is a problem.
As you can see the Descr-values in both sheets are slightly different.
That is because they are from different sources.

I want to use a VLOOKUP to put the Attribute_ID
This is the formula placed on sheet1 in cell C2:
=vlookup(B2;Sheet2!$C:$D;2;0)

I don't get values now... because column Sheet2!C contains a formula.
But I do not want to remove this formula.
My users have to modify Sheet2!B2 from "Nr-cookies" to "cookies" and then
the vlookup should place the correct Attribute_ID in Sheet1.

How do I keep the formula in Sheet2 column C and still get a working VLOOKUP
based on that column?

 

[Gareth_Evans (InterCall EMEA)]

Hi, would it be possible to copy & paste 'values' into another column and use
this for reference for the look up?

I don't know if Excel can match up values that aren't the same in the actual
cell (as opposed to it looking visually the same).

Not a great fix but it might be easier than modifying the cookies column.

Best regards,

Gareth

 

[Pluggie]

Hi Gareth,

I saw that I messed up the column numbers in my original story, so the
formulas didn't work. I corrected them below.

Thanks for thinking with me...
of course that would make the vlookup function properly...
but it won't do what I want anymore.

My users have to modify the value of Sheet2!B2, which in turn will change
the formula-result of Sheet2!C2, which in turn will change the result of the
formula on sheet1!C2

So I actually want to match up values that only visually look the same.

Can anybody else help me?