I need to search multiple values whether they exists on another sheet

[Binu]

I have uploaded the file. I need to check the values of columns c and d of 'input' sheet in columns c and d of 'final' sheet. If any value already there return the part number otherwise say unique. Could you please share your ideas.

 

[Luke M]

Hello, and welcome to the forum!

Assuming the formula goes on Input sheet:
=IFERROR(INDEX(Final!A:A,(1/SUMPRODUCT((Final!$C$2:$C$25=C2)*(Final!$D$2:$D$25=D2)*ROW(Final!$D$2:$D$25)))^-1),"Unique")

Copy down as needed. I set this to look at 25 rows on Final sheet, but may need to adjust.

 

[Abhinav Shori]

In the file attached you'd just need to press the button to get the desired solution.

AB

 

[Binu]

Assuming the formula goes on Input sheet:
=IFERROR(INDEX(Final!A:A,(1/SUMPRODUCT((Final!$C$2:$C$25=C2)*(Final!$D$2:$D$25=D2)*ROW(Final!$D$2:$D$25)))^-1),"Unique")

Thank you very much for help. But a small problem. If any of the values of columns c and d of 'input' sheet match columns c and d of 'final' sheet, then it should return the part number. Unique means all the values should not match. Its my mistake to describe it earlier. sorry about that.

 

[Luke M]

So, you're saying that both columns don't have to match? What if col C is found in row 3, and col D is found in row 4...what happens then?

 

[Binu]

thank you very much for consideration Luke. we need to look each values of input sheet to final sheet. i am uploading a pic. i think that will help u. thank u again for helping me.

 

[Luke M]

In your picture, how does the 2nd/bottom set of arrows work? The number circles doesn't seem to appear anywhere.

 

[Binu]

Yes, that is what we want actually. if that entry not anywhere in 'final' sheet it should say 'unique' otherwise return the 'part number'. All the values of each row (eg: c2 and d2 represents one part)
unique = None of the values of each row (eg: c2 and d2) do not match
return part number = Any of the values of each row (eg: c2 and d2) match.

 

[Luke M]

Hi Binu,

forgive me if I'm being a bit slow today. You could re-upload your file, but manually fill in the rows showing whether they should be counted as unique or the p/n? This might help me with the formula.

 

[Binu]

here is the file

 

[Luke M]

That file has my formula filled in...are you saying that the formulas are right? If formulas are wrong value, I need you to fill in what they should be.

 

[Binu]

I have uploaded the file with errors

 

[Luke M]

Thanks Binu.

Try changing formula to this:
=IFERROR(INDEX(Final!A:A,(1/SUMPRODUCT(((Final!$C$2:$C$24=C2)+(Final!$D$2:$D$24=D2)>0)*ROW(Final!$D$2:$D$24)))^-1),"Unique")