Get next value from a column

[xSWAPNILx]

Hi,

Please help me with the excel problem. In B1 the user will enter any column name from Sheet2 then B5 will display the next value in Column B (the column entered in B1) which is greater then the value in B3 in case there is no value greater then B3 then it will display the last value in the column.

Sample sheet is attached.

Thanks in advance.

[sktneer]

Hi Swapnil,
Welcome to the forum.

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

On Sheet1
In B5

=IFERROR(INDEX(INDIRECT("Sheet2!" &B1&"2:"&B1&6),SMALL(IF(INDIRECT("Sheet2!" &B1&"2:"&B1&6)>B3,ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),1)),INDEX(INDIRECT("Sheet2!" &B1&"2:"&B1&6),MATCH(9^9,INDIRECT("Sheet2!" &B1&"2:"&B1&6))))

***********************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[:) Sixthsense :)]

In B5 Cell

=IFERROR(SMALL(INDEX(Sheet2!$A$2:$C$6,,MATCH(B1,Sheet2!$A$1:$C$1,0)),COUNTIF(INDEX(Sheet2!$A$2:$C$6,,MATCH(B1,Sheet2!$A$1:$C$1,0)),"<="&B3)+1),B3&" Is the Higher Value")

[AlKey]

Or

=AGGREGATE(15,6,Sheet2!A2:C6/((Sheet2!A1:C1=B1)*(Sheet2!A2:C6>B3)),1)

[Indi_Ra]

@ : ) Sixthsense : ) ,

Your formula does not comply with the last condition of OP

in case there is no value greater then B3 then it will display the last value in the column.

@AlKey,

If the selected column is C and the value is 6, your formula crashes.

[AlKey]

@ : ) Sixthsense : ) ,

Your formula does not comply with the last condition of OP


@AlKey,

If the selected column is C and the value is 6, your formula crashes.

Your observation is wrong. If there is no value greater than 6 in column C the formula would displace error. If desired, an error trapping can be used mask it.

[Indi_Ra]

Your observation is wrong. If there is no value greater than 6 in column C the formula would displace error. If desired, an error trapping can be used mask it.

It is not necessary error trapping, it should display last value in that column.

xSWAPNILx
....in case there is no value greater then B3 then it will display the last value in the column.

[AlKey]

It is not necessary error trapping, it should display last value in that column.

My sincere apologies, I simply did't read the whole thing.

Corrected version

=IFERROR(AGGREGATE(15,6,Sheet2!A2:C6/((Sheet2!A1:C1=B1)*(Sheet2!A2:C6>B3)),1),LOOKUP(10^308,INDEX(Sheet2!A2:C6,0,MATCH(B1,Sheet2!A1:C1,0))))

[samba_ravi]

=IFERROR(IFERROR(INDEX(INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0)),MATCH(TRUE,INDEX(INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0))>$B$3,0),0)),LOOKUP(10^10,INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0)))),"")

=IFERROR(IFERROR(INDEX(INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0)),MATCH(TRUE,INDEX(INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0))>$B$3,0),0)),LOOKUP(10^10,INDEX(Sheet2!$A$2:$C$6,,MATCH(B$1,Sheet2!$A$1:$C$1,0)))),"")

Try this

[timiop2011]

=IF(IF(AND(B1="A",B3>=MAX(Sheet2!A2:A6))=TRUE,INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A),1),IF(AND(B1="B",B3>=MAX(Sheet2!B2:B6))=TRUE,INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B),1),IF(AND(B1="C",B3>=MAX(Sheet2!C2:C6))=TRUE,INDEX(Sheet2!C:C,COUNTA(Sheet2!C:C),1))))=FALSE,IFERROR(SMALL(INDEX(Sheet2!$A$2:$C$6,,MATCH(B1,Sheet2!$A$1:$C$1,0)),COUNTIF(INDEX(Sheet2!$A$2:$C$6,,MATCH(B1,Sheet2!$A$1:$C$1,0)),"<="&B3)+1),B3&" Is the Higher Value"),IF(AND(B1="A",B3>=MAX(Sheet2!A2:A6))=TRUE,INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A),1),IF(AND(B1="B",B3>=MAX(Sheet2!B2:B6))=TRUE,INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B),1),IF(AND(B1="C",B3>=MAX(Sheet2!C2:C6))=TRUE,INDEX(Sheet2!C:C,COUNTA(Sheet2!C:C),1)))))

edit: just seen nflsales formula. It returns the same results as this formula, but is more efficient so I would use nflsales formula.