Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First column of range in VLOOKUP contains formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First column of range in VLOOKUP contains formula
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" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First column of range in VLOOKUP contains formula
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? "Gareth_Evans (InterCall EMEA)" wrote: 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" wrote: 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 B2: =vlookup(A2;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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and Referencing a Range in a Formula | Excel Worksheet Functions | |||
Formula for Range area within VLookup? | Excel Worksheet Functions | |||
How do I copy a vlookup formula without changing the data range? | Excel Worksheet Functions | |||
VLOOKUP when a range resides in Column A | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) |