I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID).

I've attached the spreadsheet to help illustrate:

e.g. on column A Sheet A (Product Sku) search for the corresponding Product ID (column B sheet B) and enter the corresponding Product ID value on Sheet A column B (Product ID).

Current formula : =IFERROR(ArrayFormula(VLOOKUP(A1:A,SheetB!A1:C,{2,3},0)),)

We'll have hundreds of products codes which will be added to frequently.

I've tried reading various online answers but I'm very confused

1 Answer 1


The easier way to solve the problem is to remove the blank rows below the data range (from row 12 to the last row of the sheet)

Removing IFERROR the cell with the formula shows the following error

Replacing A1:A by A1:A11

If we replace A1:A11 by A2:A12 we get the following

  • 1
    That's great Ruben thanks so much! all seems to be working perfectly :) Saved me lots of time!
    – Stephen
    May 28, 2020 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.