Auto Populate values if it meets condition

Hi,

I have a table which has cost values for every product, But in another worksheet, I want excel to only populate list of products that cost more than $30. How do I do that? Can it be done by writing formulas?

Example:
Product (column A) Price (Column B)
A $40
B $60
C $20
D $10
E $82
F $5
G $93
H $35


I want the result as:
Product (column A) Price (Column B)
A $40
B $60
E $82
G $93
H $35

Hello and welcome to the forum. There are multiple ways that this can be done. Here are two:

Formulas:
A2 =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Table2[Price]>30,ROW(Table2[Price])),ROWS($1:1))),"") Ctrl Shift Enter
Dragged over to B2 then down as far as needed.

Power Query:
Select A1:B9 > Data > From Table > Select the Price drop down > Number Filters > Greater Than > 30 > OK > Close & Load

See attachment for both options.

Thank you so much! Both worked! Owe you a lot!

I would like to understand the formula. Could you please help me with it?

Thanks.

You're welcome. Happy to help.

I would like to understand the formula. Could you please help me with it?

Sure. {=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Table2[Price]>30,ROW(Table2[Price])),ROWS($1:1))),"")}

This is a typical way to return multiple matches. Let's work from the inside out.

ROWS($1:1) = 1 so the SMALL function will return the 1st row # where the Price > 30 which is row 2. The INDEX function then returns the value that is in the 2nd row of column A.

As the formula is dragged down, ROWS($1:1) changes to ROWS($1:2) which would return 2 and thus return the 2nd row # where the Price > 30.

And so on.

When you drag the formula to the right, everything stays the same except INDEX(A:A changes to INDEX(B:B so the column to be returned shifts from Product to Price.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Originally Posted by 63falcondude

You're welcome. Happy to help.


Sure. {=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Table2[Price]>30,ROW(Table2[Price])),ROWS($1:1))),"")}

This is a typical way to return multiple matches. Let's work from the inside out.

ROWS($1:1) = 1 so the SMALL function will return the 1st row # where the Price > 30 which is row 2. The INDEX function then returns the value that is in the 2nd row of column A.

As the formula is dragged down, ROWS($1:1) changes to ROWS($1:2) which would return 2 and thus return the 2nd row # where the Price > 30.

And so on.

When you drag the formula to the right, everything stays the same except INDEX(A:A changes to INDEX(B:B so the column to be returned shifts from Product to Price.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thank you so much!

Originally Posted by 63falcondude

Hello and welcome to the forum. There are multiple ways that this can be done. Here are two:

Formulas:
A2 =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Table2[Price]>30,ROW(Table2[Price])),ROWS($1:1))),"") Ctrl Shift Enter
Dragged over to B2 then down as far as needed.

Power Query:
Select A1:B9 > Data > From Table > Select the Price drop down > Number Filters > Greater Than > 30 > OK > Close & Load

See attachment for both options.

Hi,

While the question I asked was only one part of the stuff I am doing on my excel sheet, it also has too many other data columns. I also have multiple pivots and formulas that make my dashoard. It's ~47 MB at the moment, and there's no problem, but the moment I added the array formula, it extremely slowed down my dashboard (like I am not exaggerating, but simply inserting one column in the sheet takes upto 4 minutes). When I delete the table with array formula (there are two columns in this table, one column has array formula, and the other column yields values corresponding to array formula results using vlookup), the excel sheet is back to normal.

I cannot use power query, but is there any other way? like a VBA code?