CODEWITHSUNDEEP
excelexcel-formulams-office
Deke
Deke

Reputation: 495

Excel Formula IF(AND?

I'm trying to write a formula that will look at Column A and match the 2 digit letter code to a linked spreadsheet in Column G:G and if they match check to make sure a certain text phrase is there, if so grab and input the date from the Cell in B. For example:

Caption1

Column A has the Prefix PA, In column K I have the current formula

=IF(AND(A2='[Fall19 Schedule Demo.xlsx]Ashro'!G:G, '[Fall19 Schedule Demo.xlsx]Ashro'!B:B="Final Retail Review by MBD"),'[Fall19 Schedule Demo.xlsx]Ashro'!$C$30,"No")

The idea is, it looks at column A2 and if that = the PA in Column G from caption 2

Caption2

Then it will look at column B and make sure it also says "Final Retail Review by MBD" and if so it will place the date from Column C (In this case 5/9/2019) otherwise it will say "No".

The problem is that even though this criteria is met my formula doesn't seem to want to pull the date, and I'm not sure why.

Any help would be greatly appreciated. Also I'm sure there is an easier way to do this that I'm not thinking of so I'm open to any suggestions.

Thanks!!! -Deke

Upvotes: 0

Views: 71

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You want to use COUNTIFS() to find if it exists:

=IF(COUNTIFS('[Fall19 Schedule Demo.xlsx]Ashro'!G:G,A2,'[Fall19 Schedule Demo.xlsx]Ashro'!B:B,"Final Retail Review by MBD"),'[Fall19 Schedule Demo.xlsx]Ashro'!$C$30,"No")

On note trying to match on a long string may return false negatives, in that it is looking at a letter for letter match and if there is a misspelling or added spaces then it will return FALSE.

Upvotes: 2

Related Questions