LinkBack URL
About LinkBacks
look at results on j1 downwards
i need something like what AZUMI has done on g1 downward , Array formula ,which does keyword search
Look up based on column A
Find file Attached
look at results on j1 downwards
i need something like what AZUMI has done on g1 downward , Array formula ,which does keyword search
Look up based on column A
Find file Attached
You have an answer so what is different to results in G ????
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Hi,
you want all the words that end with NET?
Try
=IFERROR(INDEX($A$1:$A$44444,AGGREGATE(15,6,ROW($A$1:$A$1000)/(RIGHT($A$1:$A$44444,LEN($I$1))=$I$1),ROWS($1:1))),"")
You should have been able to do this as this type request from you has been repeated numerous times. As Ali said in an earlier note , learn to do things for yourself for once!
and better description would be "List all words ending NET" or "List all words ending criteria in $I$1""
Last edited by JohnTopley; 02-28-2022 at 04:50 PM.
Thank you John that was beyond quick , the first answer searches NET everywhere this one searched at the end
Here is mine:
=IFERROR(INDEX($A$1:$A$44444,SMALL(IF((RIGHT($A$1:$A$44444,3)="NET"),MATCH(ROW($A$1:$A$44444),ROW($A$1:$A$44444))),ROW(A1))),"")
Will the LEFT word for say if I put RAT on L2 on wona pour words not end with RAT instead starting with ?
So that i dont have to start another thread ,look at the RAT thingi
If you want words ending with RAT
=IFERROR(INDEX($A$1:$A$44444,SMALL(IF((RIGHT($A$1:$A$44444,3)="NET"),MATCH(ROW($A$1:$A$44444),ROW($A$1:$A$44444))),ROW(A1))),"")
change the word I coloured red to RAT.
Originally Posted by makinmomb
=iferror(index($a$1:$a$44444,small(if((left($a$1:$a$44444,3)="rat"),match(row($a$1:$a$44444),row($a$1:$a$44444))),row(a1))),"")
Simply change RIGHT to LEFT
Not working , editted the formula ,copy down not working
See attached...
=iferror(index($a$1:$a$44444,small(if((left($a$1:$a$44444,3)="rat"),match(row($a$1:$a$44444),row($a$1:$a$44444))),row(a1))),"")
belinda work but need dynamic if i change L2 to say NOT then wont work , need that kind of JOHN T
=iferror(index($a$1:$a$44444,aggregate(15,6,row($a$1:$a$1000)/(left($a$1:$a$44444,len($l$1))=$l$1),rows($1:5))),"")
OR
=IFERROR(INDEX($A$1:$A$44444,SMALL(IF((LEFT($A$1:$A$44444,3)=$L$1),MATCH(ROW($A$1:$A$44444),ROW($A$1:$A$44444))),ROW(A1))),"")
Change the ROWS from ROWS(1:1) or whatever you started with
,ROWS($1:1))),"")
always use this construct and you will not go wrong. The row increments by 1 as you drag down but you must set first parameter as ABSOLUTE
Last edited by JohnTopley; 02-28-2022 at 05:46 PM.
belinda2ooo thank you , now works
Last edited by makinmomb; 02-28-2022 at 05:36 PM. Reason: g
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks