If Date matches selection, double points assigned -

[sugardaddy095]

I'm trying to have A102 (Date Entered by user) if it matched any of the cells below, then it doubles the point value from the list. The values are not properly doubling, however, when stepping through the equation it works. Help!! ive been working on this for forever.




=IF(A102=OR($A$21,$J$24,$P$27,$K$30,$O$27,$J$30,$P$44,$J$46,$O$50,$P$50,$J$53,$K$53,$S$50,$Y$47,$X$53,$G$61,$A$64,$B$64,$C$64,$G$64,$A$67,$B$67,$C$67,$X$61,$Y$61,$S$64,$T$64,$N$90,$O$90,$P$90,$X$90,$Y$90,$S$93,$T$93,$U$93,$V$93,$W$93,$X$93,$Y$93,$S$96),(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE)*2),(IFERROR(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE),"")))

 

[AliGW]

Then this:

=IFERROR(IF(OR(A102=$A$21,A102=$J$24,A102=$P$27,A102=$K$30,A102=$O$27,A102=$J$30,A102=$P$44,A102=$J$46,A102=O$50,A102=$P$50,A102=$J$53,A102=$K$53,A102=$S$50,A102=$Y$47,A102=$X$53,A102=$G$61,A102=$A$64,A102=$B$64, A102=$C$64,A102=$G$64,A102=$A$67,A102=$B$67,A102=$C$67,A102=$X$61,A102=$Y$61A102=S$64,A102=$T$64,A102=$N$90,A102=$O$90A102=P$90,A102=$X$90,A102=$Y$90,A102=$S$93,A102=$T$93,A102=$U$93, A102 =$V$93,A102=$W$93,A102=$X$93, A102=$Y$93,A102=$S$96),VLOOKUP($C102,$AR$7:$AT$30,3,FALSE)*2),VLOOKUP($C102,$AR$7:$AT$30,3,FALSE),"")),"")

However, it would be much better if you listed those dates in one contiguous array and then used a VLOOKUP to determine if A102 matched any of them for the first section of the IF statement.

 

[sugardaddy095]

Correct:
1) A102 = American dates inputted. Formatting is set identically in the calendar and the cells being input - A102 (1/1/17)
2) Depending on the Description entered as shown above, they get a point assigned that is pulled from a table show below.

A	Award Day	0
B	Bereavement	0
C	Call Out	1
D	Early Departure	0.5
F	Family Medical Leave Act	0
H	Holiday Pay	0
I	Investigative Suspension	0
J	Jury Duty	0
L	Late	0.5

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[AliGW]

If you would like to provide a link to your workbook via a file-sharing site, I'll take a look. It sounds to me as if you are going about this the wrong way altogether.

 

[sugardaddy095]

Then this:

=IFERROR(IF(OR(A102=$A$21,A102=$J$24,A102=$P$27,A102=$K$30,A102=$O$27,A102=$J$30,A102=$P$44,A102=$J$46,A102=O$50,A102=$P$50,A102=$J$53,A102=$K$53,A102=$S$50,A102=$Y$47,A102=$X$53,A102=$G$61,A102=$A$64,A102=$B$64, A102=$C$64,A102=$G$64,A102=$A$67,A102=$B$67,A102=$C$67,A102=$X$61,A102=$Y$61A102=S$64,A102=$T$64,A102=$N$90,A102=$O$90A102=P$90,A102=$X$90,A102=$Y$90,A102=$S$93,A102=$T$93,A102=$U$93, A102 =$V$93,A102=$W$93,A102=$X$93, A102=$Y$93,A102=$S$96),VLOOKUP($C102,$AR$7:$AT$30,3,FALSE)*2),VLOOKUP($C102,$AR$7:$AT$30,3,FALSE),"")),"")

However, it would be much better if you listed those dates in one contiguous array and then used a VLOOKUP to determine if A102 matched any of them for the first section of the IF statement.

After working on it for hours yesterday, I tried that too and it failed, so I went back to the original one i started.. I was losing my mind..

This worked!!! Thank you so much. I can now get my regular work done

 

[mole999]

equals A102

=IFERROR(IF(A102=OR($A$21,$J$24,$P$27,$K$30,$O$27,$J$30,$P$44,$J$46,$O$50,$P$50,$J$53,$K$53,$S$50,$Y$47,$X$53,$G$61,$A$64,$B$64, $C$64,$G$64,$A$67,$B$67,$C$67,$X$61,$Y$61,$S$64,$T$64,$N$90,$O$90,$P$90,$X$90,$Y$90,$S$93,$T$93,$U$93,$V$93,$W$93,$X$93, $Y$93,$S$96),(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE)*2),(IFERROR(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE),""))),"")

and your table is AR7 to AT30 which is only 9 entries not the 23 OR is it a bigger list

what drives the change in C102 to give you the LETTER in the VLOOKUP as a reference

 

[AliGW]

This worked!!! Thank you so much. I can now get my regular work done

OK, but please consider reworking your workbook to make this more straightforward. That formula is pretty unwieldy!