Formula works in one cell but not the others

[Colbean1]

I am having problems with getting a formula to work in other cells. It worked in the first one but not the others. What am i doing wrong?

Formula I used: =IF(AND($B$5:$B$7257="WC4",$A$5:$A$7257=$N$8),VLOOKUP($N$8,$A$5:$L$7257,12,FALSE),"")

And the spreadsheet looks like this: (sorry could not figure out how to shrink it up a bit.)

Date

User ID

Due Date

SSN CLAIM NUMBER ERROR

Action Taken

Date

Checked by Lead or Supervisor

Day

WC4

ED3

HIN

EMR

No action

Day

WC4

ED3

HIN

EMR

8/1/2018

WC4

926-05-1073 2017-000060155-0006 000-00-0000 ZERO SSN

8/15/2018

8/1/2018

P

9

10

5

0

8/1/2018

WC4

926-05-1074 2017-000060156-0004 000-00-0000 ZERO SSN

8/15/2018

C

9

10

5

0

8/1/2018

WC4

926-05-1075 2017-000060157-0002 000-00-0000 ZERO SSN

8/15/2018

Date

8/15/2018

8/1/2018

WC4

926-05-1076 2017-000060159-0009 000-00-0000 DUPLICATE SSN

8/15/2018

8/2/2018

P

12

0

0

0

8/1/2018

WC4

926-05-1077 2017-000060162-0009 000-00-0000 ZERO SSN

8/15/2018

C

12

0

0

0

8/1/2018

WC4

926-05-1078 2017-000060163-0007 DUPLICATE SSN

8/15/2018

Date

8/1/2018

HIN

926-05-1079 2017-000060175-0000 DUPLICATE SSN

8/15/2018

8/3/2018

P

0

0

0

0

8/1/2018

HIN

926-05-1080 2017-000060176-0009 DUPLICATE SSN

8/15/2018

C

0

0

0

0

8/1/2018

HIN

926-05-1081 2017-000060182-0003 000-00-0000 ZERO SSN

8/15/2018

Date

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="193" style="width: 145pt; mso-width-source: userset; mso-width-alt: 7058;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;" span="2"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;" span="2"> <tbody> </tbody>

 

[Joe4]

Welcome to the Board!

This part is not valid syntax:

[COLOR=#333333]AND($B$5:$B$7257="WC4",$A$5:$A$7257=$N$8)[/COLOR]

Think about it logicially. How can a whole range of cells equal a single value?
What are you trying to do there? Are you trying to check to see if those values ("WC4" and $N$8) appear anywhere in those respective ranges?
If so, you will need to use a different method, like COUNTIF, i.e.

=AND(COUNTIF([COLOR=#333333]$B$5:$B$7257,"WC4")>0,COUNTIF([/COLOR][COLOR=#333333]$A$5:$A$7257,$N$8)>0)[/COLOR]

 

[Colbean1]

Thanks I figured it out. This was very helpful.

 

[Joe4]

You are welcome.