Hi,
my input would be a chemical (eg indium phosphide). That input on sheet1 shall be compared with an existing list of critical raw materials on another sheet, and if it finds parts of the input search term in the list give out the name of the value in the CRM-list (in that case indium) on sheet1.
Is there a way to do that?
Please let me know if I missed crucial information or posted in the wrong forum.
Thanks for your answers, I m curious if someone s got a nice solution.
Delete the - from column C.
In F7, copied down:
=IF(C7="","",INDEX('Valuable_&_CRM'!A:A,SUMPRODUCT(--(ISNUMBER(SEARCH('Valuable_&_CRM'!A$3:A$55,C7)))*ROW('Valuable_&_CRM'!A$3:A$55))))
In Germany, you may need to use ; instead of ,
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
thanks for your fast reply. I translated your formula and it worked well for indium phosphide. I copied it down and I m getting a #SPILL error. Any ideas? Here are some more substances I tested with:
ethylvinylacetate
polyethylene terephthalate
polyvinylfluoride
copper
polyethylene
lead
copper
tin
1-propanol
On a side note: Do you have a recommendation where I can read up, so I understand what your formula is doing?
Best
Marvin
Hi. Certified Reference Materials!! Ha. Brings back memories of making them!!
I've only had 365 for a few days and am stillgetting to grips with #SPILL.
=IF(C7="","",IFERROR(INDEX('Valuable_&_CRM'!A:A,1/(1/SUMPRODUCT(--(ISNUMBER(SEARCH('Valuable_&_CRM'!A$3:A$55,C7)))*ROW('Valuable_&_CRM'!A$3:A$55)))),""))
fixes it.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Best way to see what's happening is to cut the range down to about 6 or so rows and enter one search term that IS present. Then use Formulas/Formula auditing/evaluate formula to step through the formula. If you get stuck... shout.
You need to cut it down to about 5-10 rows 'cos (very annoyingly) the dialogue box cannot be resized.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks