|I am assuming two things:|
1 - You only need to pull the brand from one report or the other, even if the part number appears in both reports.
2 - Every part number on Sheet3 can be found on either Sheet1 or Sheet2 or both.
If those assumptions are correct, then this should work for you:
Here's what's going on...
The IF(ISNA(VLOOKUP...)) tries to VLOOKUP the value in A1 in Sheet1!A1:D7. If that VLOOKUP returns a #N/A error, meaning that the value in A1 was not found, the INDEX-MATCH section looks up A1 in Column C of Sheet2 and returns the value from Column B. (We have to use INDEX-MATCH because we are looking left for the "brand" and VLOOKUP can only look right.) However, if the IF(ISNA(VLOOKUP...) is FALSE, meaning that the value in A1 was found on Sheet1, then the value_if_false argument of the IF statement does a VLOOKUP to return the "brand" from Sheet1.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.