Formula to compare two different data sets

Hey everyone! I have a fairly complicated formula question.

So I'm comparing two data sets from two places where I can sell a product. I want to compare the two to find out where I can sell a product for the most. These data sheets often comprise more than 10,000 line items, so it's very difficult and time consuming to do it by hand. The other thing is that several of the products have different versions that are done by other companies, and the version matters. I was unsure if VLookup is the right formula to use.

What I'm looking to do is compare A2 on sheet 1 with column A on Sheet 2. Then if it finds a match (say on A7), to compare B2 on sheet 1 with B7 on sheet 2. Then, if both of those match, to print A2, B2, C2 from sheet 1 and B7 from sheet 2 on a third sheet. It would also be acceptable if it copied B7 to D2 on Sheet 1.

I know this is confusing, so I've made an example of each sheet and the desired outlook.

Here is an example of what I'm working with:

Sheet 1:

NAME COMPANY PRICE
Smash Up ACD 15
Smash Up Alliance 12
Flash Point Alliance 15
Incan Gold Alliance 17
King of Tokyo Southern 21
Carcassonne Southern 13
Flash Point ACD 23
Incan Gold ACD 41
Carcassonne Alliance 21
King of Tokyo Exchange 33



Sheet 2:

NAME COMPANY PRICE
Smash Up ACD 57
Smash Up Alliance 62
Flash Point Alliance 48
Incan Gold Alliance 29
Mage Wars ACD 44
Carcassonne Southern 49
Flash Point ACD 21
Incan Gold ACD 12
Carcassonne Alliance 72
Power Grid ACD 44
Agricola ACD 49
Settlers Alliance 29


Desired output:

NAME COMPANY PRICE1 PRICE2
Smash Up ACD 15 57
Smash Up Alliance 12 62
Flash Point Alliance 15 48
Incan Gold Alliance 17 29
King of Tokyo Southern 21 0
Carcassonne Southern 13 49
Flash Point ACD 23 21
Incan Gold ACD 41 12
Carcassonne Alliance 21 72
King of Tokyo Exchange 33 0
Mage Wars ACD 0 44
Power Grid ACD 0 44
Agricola ACD 0 49
Settlers Alliance 0 29


Again, I was unsure if this could be accomplished with a simple VLOOKUP script, or if this would have to be done through VBA. I appreciate your help and any input to make this simpler.

=IFERROR(IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2;" ";REPT(" ";99));1+99*(ROW(OFFSET($A$1;;;1+LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))-1);99));$C$2:C13)))=TRUE;LOOKUP(2^15;SEARCH(C2;$C$2:$C$11);$A$2:$A$11)&" "&LOOKUP(2^15;SEARCH(A2;$A$2:$A$11);RIGHT($C$2:$C$11;2));"-");"-")

maybe it works, pl see the file

Azumi

OMG, impressive as heck formula looking up and separating text from numbers, Azumi.

But I'm pretty sure OP means the data is broken up into columns Name=A2, Company=B2, Price=C2 for each sheet.

Missed something, revised the formula

Oh ok Yup I thought that's in one cell, sorry for my stupidity

Fix it again

Azumi

Very awesome! I can't believe that you understood what I was trying to express and made a formula that does it! I am having one issue though, and I'm sure it goes back to my poor understanding of Excel scripting -- When I edit the formula to handle a list larger than 12 items, it simply repeats the first 12 items it searches for over and over again. Is there any way you can edit the formula to compensate a list of, say, 20,000 lines?

Thank you again, and I appreciate your help immensely.