I have a data table (see attached file) where I want to capture the ROW and COLUMN header for the sequential/unique data values [C18:C21]. I have tried INDEX/MATCH and cannot seem to create the proper syntax to make this happen. Thanks for your help!
Will there ever be duplicate values in the table B2:L12?
If so,
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Yes, there could be duplicate data values. :-(
See if you can adapt this...Originally Posted by fearonc
https://www.excelforum.com/showthread.php?p=3643149
Good luck!
Last edited by Tony Valko; 08-27-2015 at 08:18 PM.
Try theseARRAY formulas, adjusted from a formula from sktneer....
http://www.excelforum.com/excel-gene...a-formula.html
A18=INDEX($B$1:$L$1,SMALL(IF(ISNUMBER(SEARCH($C18,$B$2:$L$12)),COLUMN($B$1:$L$1)-COLUMN($B1)+1),1))
B18=INDEX($A$2:$A$12,SMALL(IF(ISNUMBER(SEARCH($C18,$B$2:$L$12)),ROW($A$2:$A$12)-COLUMN($B1)+1),1))
...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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Reversing A18= into the B18 cell and B18= into the A18 cell achieved exactly what I needed! Thanks so much!!
Those formulas don't account for duplicates.
You are correct Tony
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks