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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,721
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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

Board Regular
Joined
Nov 17, 2017
Messages
201
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:

emukiss10

Board Regular
Joined
Nov 17, 2017
Messages
201
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,270
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top