=SEARCH(E2,Table1[Company])
returns #VALUE! error
though I believe I have the formula written correctly...
I attach the sample file for reference.
Please help.
Thanks.
=SEARCH(E2,Table1[Company])
returns #VALUE! error
though I believe I have the formula written correctly...
I attach the sample file for reference.
Please help.
Thanks.
Last edited by mikehk; 07-20-2020 at 07:23 AM.
You have #VALUE! in cel a2.
If you change that in Facebook the result in cell H2 = 1.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
sorry, I don't get it...can you please explain more clearly.
when I remove the data type it works
Please see the attached file (with new example added).
Thanks.
use this formula instead.
=Match($E$2,$A$9:$A$14,0)
The result is 4 (4th item).
The match function works well..thanks.
I want to use the search function as the list is long the user cannot enter the exact name
for the match function to work
Duplicated post.
with defined names in column N. => Formula => define names
And datavalidation in Cell E2.=> Data => datavalidation.
See the attached file.
Thanks a lot buddy...I got it working. Please see the attached working formula.
The only problem I see is that the data type doesn't work so will make an helper column to solve this issue.
Thanks a lot.
Have a great week.
You have a datavalidation refering to "=_xlfn.ANCHORARRAY($H$2)"
Can you explain the red part of the formula.
formula in cell $H$2 is
=SORT(FILTER(Table1[Company],ISNUMBER(SEARCH($E$2,Table1[Company])),"Not found"))
Hope it helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks