Forum Discussion

IamRahulV's avatar
IamRahulV
Copper Contributor
Mar 07, 2023

Split text then look up a table to check if exists

Dear all

 

I need your help in figuring this out. Using this sample scan code: 8421 BCD.

I am able to split it using the split text function

 

I am then trying to check if the text part is there in a predetermined code table using =IF(SEARCH(M3,D3,1),M3,"not found") but keep getting "#value" error:

 

I tried nesting into 1 single formula, with same error

 

abbiha
cdga 
what 

 

Please advise

Warm regards

excel

1 Reply

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 07, 2023

    IamRahulV 

    =IF(SUM(N(ISNUMBER(SEARCH(A7,$A$2:$C$4)))),A7,"not found")

    Does this return the expected result? Enter the formula as an arrayformula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. 

Resources