Match two columns and return values based on a condition in two other columns-CodePudding

Four total columns: Column A Part#, Column B Old Cost, Column C Part#, Column D New Cost

I need to match the two Part# columns (they are the same part numbers in a different order):

Once matched, compare the accompanying two columns of Costs

If the costs match, return MATCHED

If the costs are different, return the New Cost amount

Col A Part#    Col B Old Price  Col C Part #     Col D New Price**
5555           $5.00              1111           $1.00 
4444           $4.00              2222           $2.99 
1111           $1.00              3333           $3.00 
2222           $2.00              4444           $4.99 
3333           $3.00              5555           $5.99 
8888           $8.00              6666           $6.99 
7777           $7.00              7777           $7.99 
9999           $9.00              8888           $8.99 
6666           $6.00              9999           $9.99

An example return:

Part# 1111  MATCH
Part# 2222  $2.99
Part# 7777  $7.99
Part# 3333  MATCH

CodePudding user response：

Use VLOOKUP and compare the results:

=IF(VLOOKUP(G2,A:B,2,FALSE)=VLOOKUP(G2,C:D,2,FALSE),"MATCH",VLOOKUP(G2,C:D,2,FALSE))