excel comparing 2 columns

[tonyvee1973]

Hi all any help would be amazing as this is simple but driving me nuts

I have 2 x worksheets in one excel document.
I want to compare data in column D on sheet1 to data column A on sheet2.
If they match i want the value on sheet2 column B to be inserted to where this formula is.

Please help!

 

[SkipVought]

Hi,

Why not simply copy the data from one sheet and paste it immediately below the data on the other sheet, sort the column and the Remove Duplicates feature on the Data tab of the Ribbon?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

Hi skip
Due to the nature of the sheet I need to have a formula to do this.
Any help really would be appreciated.
Thx

 

[SkipVought]

Formula?

Think about it. You need a formula for each non-matching item. Well, how many is that? Formulas do not magically appear. Could be none. Could be scads.

Maybe you need to explain WHAT needs to happen and why, in the context of your sheet, rather than HOW you think it ought to be accomplished. Maybe a small example in context would help, "due to the nature of the sheet."



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

I used the formula below to compare 2 columns and then insert data from a third if the 2 columns match but cant get it to work with just comparing one column on 2 sheets as per original post above

=SUMIFS('POS1'!$D:$D,'POS1'!$A:$A,$A3,'POS1'!$B:$B,$C3)

 

[tonyvee1973]

The formula below i used to use when comparing data in column A in both sheets then inserting data from column C in sheet 2 if match occurs but wont work with this.


=SUMIFS(Sheet2!$C:$C,Sheet1!$A:$A,$A1)

 

[tonyvee1973]

All solved using formula below and ensuring numbers were not text.
=INDEX(sheet2!$A:$A,MATCH(D1,sheet2!$D:$D,0),1)