CODEWITHSUNDEEP
excelexcel-formula
Ruchi
Ruchi

Reputation: 146

Based on one input value,populate table

How can i achieve this, I want the orange table to be auto generated, based on the value in cell "F5" i.e minutes earned more than : '410'.... F5=410

I tried using index, match but couldn't achieve.Can someone help.enter image description here

Upvotes: 0

Views: 413

Answers (3)

Jacob Edmond
Jacob Edmond

Reputation: 808

Put this is E8, drag over to F8, and then drag both down as far as needed.

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($C$6:$C$21)/($C$6:$C$21>=$F$5),ROW()-7),1),"")

enter image description here

Upvotes: 2

krib
krib

Reputation: 569

You could add a helper-column and put:

=SMALL(IF(C:C<=$F$5,ROW(C:C)),ROW())

then push ctrl+shift+enter and drag down in column D. Then use =INDEX(B:D,D2,1) add IFERRORto avoid #errors

Upvotes: 0

R3uK
R3uK

Reputation: 14537

=IFERROR(IF(VLOOKUP($E10,$B$6:$C$21,1,FALSE)<$F$5,"",VLOOKUP($E10,$B$6:$C$21,1,FALSE)),0)

But if you want to display only the one matching the criteria within continuous rows, you'll need at least a column helper storing the row coming from MATCH.

Upvotes: 0

Related Questions