Google Spreadsheet vlookup with arrayformula PLUS乘法返回值（s） [英] Google Spreadsheet vlookup with arrayformula PLUS multiplication of returned value(s)

问题描述

我有我的VLOOKUP与ArrayFormula工作感谢亚当（AD：AM）出色拼写解决方案（ https://productforums.google.com/forum/#!searchin/docs/parallel$20lookup$20solution/docs/36A0epDlIdE/qnywZst0DioJ ）

所以在col J中这是我的：

  = ArrayFormula （VLOOKUP（H2：H; ProductTable！A2：C; 3 * SIGN（ROW（H2：H））; FALSE））
  

非常棒。然而，我想然后从col J中的该公式中得到返回的值，并将其与第I行中的Qty值相乘。

显然我可以在Col K中添加一个额外的帮助列...

  = ARRAYFORMULA（I2：I * J2：J） 
  

...但我希望避免在查询结果中添加另一列，特别是在col J中返回的结果在工作表上没有明显的目的 - 它们仅用于严格的数学目的。

我尝试了多种方法将公式滑入col J的ArrayFormula / VLOOKUP函数中，但迄今为止我还没有运气。

也许它不能完成（？），或者我可以从一组新的眼睛看待这个问题。

解决方案

您引用的帖子是从2011年开始的。我认为用例已经被解决了。

我有一列使用公式 = ArrayFormula（VLOOKUP（J $ 2：J，$ C $ 2：$ D，2，FALSE））。我创建了一个包含 = VLOOKUP（J2，$ C $ 2：$ D，2，FALSE）的检查列，并拖拽了一些行。

似乎在数组公式中， J 正在迭代，就像它在我的拖动公式中一样，而 C：D 是固定的。至于拖动检查栏，原始列和检查列都有相同的结果数据。

警告：我没有做详尽的搜索或创建一个特别聪明的测试案例。我只是眼睁睁看着一大堆实际数据。

I have my VLOOKUP with ArrayFormula working thanks to Adam's (AD:AM) brilliantly spelled-out solution (https://productforums.google.com/forum/#!searchin/docs/parallel$20lookup$20solution/docs/36A0epDlIdE/qnywZst0DioJ)

So in col J this is what I have:

=ArrayFormula(VLOOKUP(H2:H;ProductTable!A2:C;3*SIGN(ROW(H2:H));FALSE))

Works great. However, I would like to then take the returned value(s) from that formula in col J and multiply it against the Qty value(s) that are in row I.

Obviously I could add an extra helper column in col K...

=ARRAYFORMULA(I2:I*J2:J)

...but I was hoping to avoid adding yet another column to my Query results, especially since the returned results in col J have no visible purpose on the worksheet - they are meant to be used strictly for mathematical purposes only.

I've attempted multiple ways of slipping that formula into the ArrayFormula / VLOOKUP function in col J, but I've had no luck thus far.

Maybe it can't be done(?), or maybe I could just benefit from a fresh set of eyes looking at this issue.

解决方案

The post you referenced is from 2011. I think that use case has been resolved since then.

I have a column using the formula =ArrayFormula(VLOOKUP(J$2:J,$C$2:$D,2,FALSE)). I created a check column with =VLOOKUP(J2,$C$2:$D,2,FALSE) which I dragged down a number of rows.

It appears that, in the array formula, J is being iterated just as it is in my dragged formula, while C:D are fixed. Both the original column and check column have the same result data so far as I've dragged the check column down.

Caveat: I did not do an exhaustive search or create a particularly clever test case. I just eyeballed a good heaping of rows of actual data.

这篇关于Google Spreadsheet vlookup with arrayformula PLUS乘法返回值（s）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！