LinkBack URL
About LinkBacks
I have used the SMALL and LARGE functions to create a bottom and top 10, but I need to include the corresponding row and column names to identify those values. I have attached a sample.
I have used the SMALL and LARGE functions to create a bottom and top 10, but I need to include the corresponding row and column names to identify those values. I have attached a sample.
In J3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER
=IFERROR(INDEX($A$1:$H$1,,MIN(IF($B$2:H13=J2,COLUMN($B$2:$H$13)))),"")
In J7 Cell - Array Formula - Requires CTRL+SHIFT+ENTER
=IFERROR(INDEX($A$1:$H$1,,MIN(IF($B$2:H13=J6,COLUMN($B$2:$H$13)))),"")
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
Drag both the formula's to right...
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
Hi wilburr,
one more approach...
In J3 use formula as..
Formula:
=INDEX($B$1:$H$1,SUMPRODUCT(($B$2:$H$13=J2)*COLUMN($B$2:$H$13))-COLUMN($B$2:$H$13)+1)
+
INDEX($A$2:$A$13,SUMPRODUCT(($B$2:$H$13=J2)*ROW($B$2:$H$13))-ROW($B$2:$H$13)+1)
format the cell as "DD-MM-YYYY HH:MM"
and copy the same in J7 too.. or drag rightward.
PS: change A2 to 0 for better view..
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
@ Debraj Roy,
The reason for avoiding sumproduct approach is it will Fail/Throw an Error if there is multiple matches. Because sumproduct accumulates all the matches
@ :) Sixthsense :)
I agree boss... :)
but for 2D search.. I know only this method.. need some time to learn more.. ;(
Keep guiding..
In fact me too thought to go for the same approach which you shown in Post #3, but because of possibility of duplicate records I need to go for Array solution
I just wanted to let you know the duplicate risk if you are not aware.
Thanks for taking it in a positive way
I used the first formula provided and it resulted in a number. I used the comments from the second responder to format the cell and it almost worked.
Two questions.
1. how do I format the cell to show the results desired in the attached/
2. Would you walk me through the first formula so I understand it better and do not need to ask the question again when working on a larger spreadsheet containing multiple weeks.
TIA
Wilburr
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks