Search for text within an Array

Hi There,

I have 2 worksheets in Excel 2013 . Worksheet1 Column A has a list of data such as below

/vol/dev_esxi_boot_luns/devesx01
/vol/dev_esxi_boot_luns/devesx02
/vol/dev_vmfs_vmstore/dev_vmfs_vmstore_lun
/vol/dev_vmfs_vmstore/dev_vmfs_vmstore_lun
/vol/dsf_data_vol/dsf_data
/vol/dsf_jd_vol/dfs_jd_data
/vol/dsf_jd_vol/dfs_jd_quorum
/vol/dsf_jd_vol/dfs-jd-namespace
/vol/esxi_boot_luns/exesx01/lun1

Woksheet2 Column A also has a list of data like below

backup_vmstore01
default_profile
dev_esxi_boot_luns
dev_vmfs_vmstore
dsf_data_vol
dsf_jd_vol
esxi_boot_luns
esxi5_syslog
excas1

Now i need another to for each row in ColumnA worksheet2 see if that text exists within Worksheet 1 ColumnA and output true or false in ColumnB worksheet2

So for instance, the 3rd line in ColumnA worksheet2 is dev_esi_boot_luns, this exists with the text on Worksheet1 ColumnA on row 1,2 and 3 and so woould be "True"

Thanks for taking the time to read this

Best Regards

Byron

Hi,

if data in worksheet 2 starts in A1, in B2

=IF(ISNUMBER(FIND(A1,Sheet1!$A$1:$A$9)),"True","False") would work
dragged down

Regards

Hi there,

Thanks for the quick response, i have done as you have asked, however it reports back as false for everything. Would this still work as suggested if there are no speaces between the text it is searching for within Worksheet1?

I have attached the document to show you.test.xlsx

Hi again!

Sorry it was my bad, I don't think my formula could work..

Try

=IF(ISNUMBER(match(A1,filer1_03062014_lun_report!B:B;0)),"True","False")

Does that work?

Hi there,

Sorry for the late response, only just logged back on. That works perfectly. Thank you so so much. I have been racking my brains for days

Hi, Sorry again. After going through it more thoroughly, that actually doesnt work. If you look in the previous attached file where there is vol0 it is coming up with true, but that doesn't exist?

Hi,

Sorry, not an Excel pro so you're not getting the best answers possible I think ^^


I actually misunderstood, I thought you wanted to compare column B in the 1st worksheet with what was in the 3rd sheet.


in sheet "filer1", in B2:

=IF(ISNUMBER(MATCH(A2,filer1_03062014_lun_report!A:A;-1)),"True","False")

that formula is close but I don't think works perfectly for you: mmu_sql_dbfiles will be true even when there's only mmu_sql_dbfiles2_vol in filer report..

No sorry that doesnt seem to be working, everything comes back as false

Fantastic, brilliant. works first time :-)

If i wanted to take a true value, how would i put an if statement in a column next to the answer to effectively if true take a value on that row and report back in that cell. So for example take the A2 in Worksheet 2, it reports back false based on your formulae so the value should take the value from C2 in that worksheet (i know nothing is there at present) if it is true i want it to report back the value associated with the row it found the text, which would C something in worksheet 1. Hope i am making sense