If string X = String Y then Take the value of Cell Z

[capitarno]

I have have a MS query in a spreadsheet whos data can change in number of rows. I need a formula to do the following:

Sheet 1 called Process Elements has a column called reference IT starts AAA.001, AAA,002 AAB.001 etc. There is a second sheet called filenames that has a coumne called Node Reference AAA.000, AAB.000 There is a third column called function.

I can get the two refernce columns to match using a LEFT 3 formula.

So the Excel formual I need would be shomething like:

Where left 3 process elements reference (B:B) = Filenames! Node reference (A:A) left 3 then cell = function

Any suggestions greatfully recieved

 

[rcbunting]

Capitano, I don't entirely understand your question, but I think this is what you are looking for:

=IF(LEFT('Process Elements'!B2,3)=LEFT(FileNames!B2,3),FileNames!C2," (not matched) ")

this will compare the 3 leftmost characters from cell B2 in the sheet "Process Elements" to the 3 leftmost characters from cell B2 in the sheet "Filenames." If they are the same, it will then take the value from cell C2 in "Filenames" -- i'm assuming that this is where your 'function' column is. If the cells do not match, then the formula will output "(not matched)." You'll need to copy this formula down so that it compare b3 to b3, b4 to b4, and so on.


GL!