Find, search formula

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
Hello!

I have a problem with formula. I Have two tables. Main and support. Support looks like t

SymbolCritFullName
One_Some_dn_1_masSOME NAME 1
One_Some_dn_11_midSOME NAME 2
Lik_mo_midSOME NAME 3
oper_nand_midSOME NAME 4
oper_nand_ODK_masSOME NAME 5
One_Some_dn_appSOME NAME 6
etcetcetc

<tbody>
</tbody>


Main looks like this

CritFullNameFILE TO GET
massSOME NAME 5oper_nand_ODK_2017_Eng_stig
massSOME NAME 5oper_nand_ODK_2018_nna
midSOME NAME 3Lik_mo_donar_rettin
midSOME NAME 4oper_nand_LOKONTray

<tbody>
</tbody>


in my main table, in column "C" I will paste some filenames. I want to fill columns A and B automatically base on column C. First part of a file has the same name as Symbol in support table. I like excel to search if any of the symbol strings match my file and if so, copy FullName to column B of main table. Than I need to check full name with Crit column and copy adequate crit to main column.

My formula so far for FullName column of main table:

=if(D2<>"",
LOOKUP(1E+100,
FIND('SUPPOERT TABLE'!$A$2:$A$119,D2),
'SUPPORT TABLE'!$C$2:$C$119),"")

and it is 99% accurate. - that is not acceptable. This formula has problem with "oper_nand_ODK_" and "oper_nand_" as I can tell but I havent check all 120 possibilities.

Please help.


Best Regards
W.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
This formula has problem with "oper_nand_ODK_" and "oper_nand_"
What problem exactly?

If ..
- the Symbol column of the first table is arranged with the "similar" strings arranged in order of increasing length, like your sample and
- there are no blanks in the Symbol column range
.. does your existing formula return the expected results?
 
Upvote 0
In A2 of Main enter and copy down:

=IF($C2="","",LOOKUP(9.99999999999999E+307,SEARCH("|"&SUPPORT!$A$2:$A$7,"|"&$C2),SUPPORT!B$2:B$7))

Adjust the sheet names if necessary.
 
Upvote 0
Thank You for replaying. I will try Aladin formula later today.
Peter, when I enter oper_nand_ODK_7946512 (random text after "_"), excel is matching it with oper_nand_. I have rearanged support table (a->z) and now it works, but something is not right, It should work erlier.
I dont like it when "I don't know how, but it works".
 
Last edited:
Upvote 0
Aladin, I've tried your formula but the result is the same. It only works If I sort my supporting table A->Z.
 
Upvote 0
Aladin, I've tried your formula but the result is the same. It only works If I sort my supporting table A->Z.

That's correct. You'll need to sort SUPPORT on column C in ascending order. Try to sollicite for a VBA routine that does the sorting whenever you add new records.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top