excel - Excel Formula Vlookup issue

Question

I have 2 excel sheets ("sheet 1, sheet 2").

Sheet 1 and Sheet 2 have "product #" in column A and "price" in column C. I'm trying to write a formula that would allow Sheet 1 to lookup on sheet 2 if there is a Product # match In column A and if there is a product # match then to update on Sheet 1 Column C (Price) the product price from sheet 2. If there is no Match of product # from sheet 2 then to leave the price on sheet 1 alone.

I'm using this formula which returns a "0" on an error but I can't figure out how to modify it to leave price alone when there's no product # found.

=IFERROR(VLOOKUP(A2,updated!A:C,3,FALSE),0)

Thanks

标签： excelformula

Answer 1

您想为此使用INDEX/MATCH：

将以下公式放入列中C并向下填充 =IFERROR(IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=Sheet1!B2,B2,INDEX(Sheet2!B:B,匹配（A2，Sheet2！A：A，0））），“”）

MATCH会发现，Sheet2 上具有相同产品 ID 的行，将INDEX返回匹配的价格。如果它与 Sheet1 上的值匹配，则公式将返回 Sheet1 列中的值B。如果不是，它将从 Sheet2 返回价格