I need a formula to output an email address on sheet output as shown in the attached file when the data in sheet "X47" column J says "Email" and the data in "X47" column A has the same date as inputted in "Output" C2. Any help would be most appreciated.
You can use this arrayed formula
=IFERROR(INDEX('X47'!$J$2:$J$100, SMALL(IF('X47'!$A$2:$A$100=$C$2, ROW($A$2:$A$100)-ROW($A$2)+1), ROWS($A$2:$A2))),"")
...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.
Your sheet is on manual calculation so hit F9 after dragging the formula down.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Try
=IFERROR(INDEX('X47'!$J$2:$J$100,SMALL(IF(('X47'!$A$2:$A$100=Output!$C$2)*('X47'!$X$2:$X$100="Email"),ROW($A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")
Enter with Ctrl+Shift+enter
Copy down
Column X has "Email" not J
As regular formula
Formula:
Please Login or Register to view this content.
Last edited by AlKey; 11-06-2015 at 10:58 AM. Reason: Updated formula
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks