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.
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
Please Login or Register to view this content.
***********************
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.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
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")
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Or
=AGGREGATE(15,6,Sheet2!A2:C6/((Sheet2!A1:C1=B1)*(Sheet2!A2:C6>B3)),1)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
@ : ) Sixthsense : ) ,
Your formula does not comply with the last condition of OP
@AlKey,in case there is no value greater then B3 then it will display the last value in the column.
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.Originally Posted by Indi_Ra
=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 thisPlease Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Formula:
Please Login or Register to view this content.
edit: just seen nflsales formula. It returns the same results as this formula, but is more efficient so I would use nflsales formula.
Last edited by timiop2011; 05-05-2015 at 09:21 AM.
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.
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))))
Last edited by AlKey; 05-05-2015 at 09:44 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks