CODEWITHSUNDEEP
google-sheets
user22726736
user22726736

Reputation: 7

How to auto-detect the number of items from FILTER and apply FORMULA

I have a Filter Formula that will fit based on conditions and produce a list of items based on::

FILTER('Sheet1'!B42:B,'Sheet1'!L42:L="Yes")
B

I will use some FORMULA (such as VLOOK, UP, and SUM...) to calculate based on the keyword from the generate list from there:

Column A Column B
Filter List Item 1 VLOOKUP ('Filter List Item 1')
Filter List Item 2 VLOOKUP ('Filter List Item 2')
Filter List Item 3 VLOOKUP ('Filter List Item 3')

The fact that the list of Filter items changes frequently raises my concern My question is, How can I determine how many items the FILTER generated in column A every times and consistently apply the corresponding formula to column B?

I anticipated, for example, that if 20 filter items appeared, 20 corresponding formulas would be applied.

Upvotes: 0

Views: 167

Answers (2)

ohmyqt
ohmyqt

Reputation: 71

The way I understand this, you only need to create IF statement if the cell is not empty..

IF(A2<>"",VLOOKUP(A2),"")

or use arrayFormula to autofill the rest of the column if row count is undefined:

=ArrayFormula(IF(A2:A<>"",VLOOKUP(A2:A,RANGE,INDEX),""))

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37050

MAP(), BYROW() will work in this situation. ARRAYFORMULA() will also work. I will go with MAP() function.

=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,"VLOOKUP ('" & x & "')"))

Adjust VLOOKUP() to your actual case.

  • Here A2:INDEX(A2:A,COUNTA(A2:A)) will return a array of values as well cell reference from A2 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

  • Then LAMBDA() will apply VLOOKUP() function for each cell of A column.

enter image description here

Upvotes: 0

Related Questions