-1

I am trying to populate rows of data from another sheet using VLOOKUP and ARRAYFORMULA.

Here is the data where I am using arrayformula:

Cells with arrayformula

G2 has the formula:

=arrayformula(if(ISBLANK(E2:E),"",VLOOKUP(E2:E,'Product List'!$A$2:$C,2)*F2:F))

And here is the "Product List" from the other sheet:

Product List

As you can see, it is bringing in the wrong numbers:

enter image description here

What is wrong with my arrayformula that is making it do this?

player0
  • 124,011
  • 12
  • 67
  • 124
Wes Foster
  • 8,770
  • 5
  • 42
  • 62

2 Answers2

3

If the content in column E is not an exact match to something in column A, it will pull the closest match. If you don't want this, you can fix this by adding FALSE as the fourth parameter in VLOOKUP. By adding FALSE as shown below, cells that are not a match will show as #N/A instead of an incorrect number.

=ARRAYFORMULA(IF(ISBLANK(E2:E),"",VLOOKUP(E2:E,'Product List'!$A$2:$C,2,FALSE)*F2:F))

Other than that, I don't see why the formula wouldn't work.

DenverCoder1
  • 2,253
  • 1
  • 10
  • 23
  • That's it! I would have tried that but I mistakingly assumed that `FALSE` was the default setting. Thank you so much! – Wes Foster Jul 09 '19 at 14:28
0

just a slight modification of the above answer to account for possible errors and render out the unnecessary syntax elements in order to make it shorter

=ARRAYFORMULA(IF(E2:E="", , IFERROR(VLOOKUP(E2:E,'Product List'!A2:C, 2, 0))*F2:F))
player0
  • 124,011
  • 12
  • 67
  • 124