LinkBack URL
About LinkBacks
I'd like to pull a list of names from column 1 where "pst" exists in column 5.
In this instance the list would look something like this :
column 5
beverly
ginger
jame pst pst beverly pst pst pst ginger pst pst pst
I'd like to pull a list of names from column 1 where "pst" exists in column 5.
In this instance the list would look something like this :
column 5
beverly
ginger
jame pst pst beverly pst pst pst ginger pst pst pst
in Column 6, enter =IF(UPPER(TRIM(B4))="PST",$A4,""). Just change the reference to B1 if your list starting from A1
=IFERROR(INDEX($A$2:$A$5,SMALL(IF($E$2:$E$5="pst",ROW($A$2:$SA$5)-ROW($A$2)+1),ROWS($A$1:$A1))),"")
confirmed wit CTRL+SHIFT+ENTER not just ENTER and copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
How would it change if I was trying to look at range, say based on the instances of "pst" in both column 4 and 5?
=IFERROR(INDEX($A$2:$A$5,SMALL(IF($D$2:$D$5="pst",IF($E$2:$E$5="pst",ROW($A$2:$SA$5)-ROW($A$2)+1)),ROWS($A$1:$A1))),"")
Can I use defined Names? Like such:
=IFERROR(INDEX(SMCatMerchs,SMALL(IF(CycleA="pst",ROW(SMCatMerchs)-ROW('2013'!$C$18)+1),ROWS('2013'!$C$1:$C1))),"")
---------- Post added at 09:57 AM ---------- Previous post was at 09:55 AM ----------
I suppose it'd be helpful to point out that I want the List to generate on a separate tab.
Yes as long as the named ranges are all same size...
=IFERROR(INDEX(SMCatMerchs,SMALL(IF(CycleA="pst",ROW(SMCatMerchs)-MIN(ROW(SMCatMerchs))+1),ROWS($C$1:$C1))),"")
CSE confirmed.
Apologies. I can't seem to get this to work.
Here's an example of what I'm trying to do :
Your named range for the managers is Managers... not SMCatMerchs
Try:
=IFERROR(INDEX(Managers,SMALL(IF(CycleA="pst",ROW(Managers)-MIN(ROW(Managers))+1),ROWS($C$1:$C1))),"")
Also, your CycleA is 3 columns wide, so this will return the names in pst is any of those 3 columns... (assumes once max per row)
Unfortunately, I keep getting a "false" reponse.
After entering the formula, are you holding the CTRL and SHIFT keys down, then pressing ENTER? You should see curly { } brackets appear around the formula.
I see the brackets, but All I retrieve on the list pafe is a "false"
You've got several formulas in one cell.
Expand the formula bar up top and you will see... remove all except the correct one, then do the CSE confirmation... I posted a workbook in my last post.
Excellent Stuff. Thank you! Now to work on the Data Validation link and it's money.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks