quick countif or index/match or vlookup formula

[banneduser123]

This may be simple, but can't figure out how to write this up yet...any help is appreciated

if column B matches column A exactly, then check if column D is contained somewhere in column E. If so, return Yes

 

[Fluff]

How about
=IF(A2=B2,IF(ISNUMBER(MATCH(D2,E2:E100,0)),"Yes",""))

 

[banneduser123]

thanks so much for the quick reply Fluff...

gonna try to clarify better here, I should note these are two different files:


if B2 (file2) is somewhere in column A (file1) (and matches exactly), then go to D2 (file2) and check if that is anywhere in corresponding cell in column E (which wouldn't match exactly)



is that a better explanation?

 

[banneduser123]

anyone else have an idea? this is kinda urgent..

 

[Fluff]

Firstly, please remember that we are all volunteers who give are time freely, as such your "urgency" is of no concern to us, so please be patient.
Secondly, when posting a question it would help if you gave all the details, rather some very vague information that bears no resemblance to your actual needs.

Now to your question.
What are the names of the workbooks
What are the names of the sheets
Which workbook/sheet is column E in
Will both workbooks be open, or do you need something that works on a closed workbook.
If the col D to col E is not an exact match, please give examples of what is in D & what is in E.
Does this need to ba a formula or are you happy with a macro?

 

[banneduser123]

I understand you're all volunteers. I just never get any solutions from this board

1 - File 2 is called "File2", File 1 is called "File1"
2 - each file has only 1 sheet. called "Sheet1"
3 - column E is in File1
4 - both workbooks are open
5 - column D contains "Ireland", column E contains "United Arab EmiratesPhilippinesIreland Indonesia"
6 - formula would be ideal

thanks

 

[Fluff]

In file2 try
=IF(ISNUMBER(MATCH(B2,[file1.xlsm]Sheet1!A:A,0)),IF(COUNTIF([file1.xlsm]Sheet1!E:E,"*"&D2&"*")>0,"yes","No"))

 

[banneduser123]

looks like it works in some cases, and others it doesn't...

for instance, for some reason it's telling me "Germany" is in a cell that says "Not Applicable"


further inspection shows me it doesn't really work out all...seems to be producing random values between Yes, No

 

[Fluff]

Without being able to see your data, it's difficult to tell.
Is Germany in B2 or D2?

 

[banneduser123]

Germany is in D2