Find, search formula

[emukiss10]

Hello!

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Symbol[/TD]
[TD]Crit[/TD]
[TD]FullName[/TD]
[/TR]
[TR]
[TD]One_Some_dn_1_[/TD]
[TD]mas[/TD]
[TD]SOME NAME 1[/TD]
[/TR]
[TR]
[TD]One_Some_dn_11_[/TD]
[TD]mid[/TD]
[TD]SOME NAME 2[/TD]
[/TR]
[TR]
[TD]Lik_mo_[/TD]
[TD]mid[/TD]
[TD]SOME NAME 3[/TD]
[/TR]
[TR]
[TD]oper_nand_[/TD]
[TD]mid[/TD]
[TD]SOME NAME 4[/TD]
[/TR]
[TR]
[TD]oper_nand_ODK_[/TD]
[TD]mas[/TD]
[TD]SOME NAME 5[/TD]
[/TR]
[TR]
[TD]One_Some_dn_[/TD]
[TD]app[/TD]
[TD]SOME NAME 6[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]


Main looks like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Crit[/TD]
[TD]FullName[/TD]
[TD]FILE TO GET[/TD]
[/TR]
[TR]
[TD]mass[/TD]
[TD]SOME NAME 5[/TD]
[TD]oper_nand_ODK_2017_Eng_stig[/TD]
[/TR]
[TR]
[TD]mass[/TD]
[TD]SOME NAME 5[/TD]
[TD]oper_nand_ODK_2018_nna[/TD]
[/TR]
[TR]
[TD]mid[/TD]
[TD]SOME NAME 3[/TD]
[TD]Lik_mo_donar_rettin[/TD]
[/TR]
[TR]
[TD]mid[/TD]
[TD]SOME NAME 4[/TD]
[TD]oper_nand_LOKONTray[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Peter_SSs]

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?

 

[Aladin Akyurek]

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.

 

[emukiss10]

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".

 

[emukiss10]

Aladin, I've tried your formula but the result is the same. It only works If I sort my supporting table A->Z.

 

[Aladin Akyurek]

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.

 

[emukiss10]

Thank You very much for Your support. Please close this thread.