vlook up + If statement

[Ninjababy1123]

Good Morning,

I am trying to combine a vlook up with an if statement

Here is my table

PLC Rate 1 Starting Date Ending Date Rate 2 Starting Date Ending Date
60 44.94 9/25/2012 12/2/2012 45.04 12/3/2012 9/24/2013
61 62.57 9/25/2012 12/2/2012 62.67 12/3/2012 9/24/2013
62 47.8 9/25/2012 12/2/2012 47.9 12/3/2012 9/24/2013
63 93.86 9/25/2012 12/2/2012 93.96 12/3/2012 9/24/2013


Here is my data

PLC Cd T/S Date
60 9/28/2012
60 9/28/2012
60 9/28/2012
60 9/28/2012
60 9/28/2012
60 9/28/2012
61 8/2/2013
61 8/23/2013
61 8/30/2013
61 9/6/2013
62 10/12/2012
62 9/28/2012


I need to vlook up the plc code and then incorporate the if statement for the date to pull back rate 1 or rate 2...any help would be greatly appreciated.

 

[sophie619]

I'm confused about what you want to accomplish. Are you trying to fill out the second table - by doing a vlookup into the first table for the PLC and then you want to fill in the rate (be it rate1 or rate2) somewhere in that second table? I think your headings are confusing me.

 

[Ninjababy1123]

The end result should be the correct rate.

The first table gives the date range for the rate so i.e. on PLC Cd 60 if the date is >12/2/12 then it is 44.94, if the date is <12/3/12 then it is 45.04.

The second table is the Codes and Dates I need to vlook up the PLC code in table 1 to table 2, then use the if statement to determine the rate based on date.

Does that make sense?

 

[Ninjababy1123]

Maybe it would help if I put in my formulas

If statement:
=IF($C3>$N$3,$O$3,$L$3)

Vlookup:
=VLOOKUP(B3,$I$3:$I$8,1,FALSE)


Poor Attempt to Combine:
=((IF(ISERROR(VLOOKUP(B3,$I$3:$I$8,1,FALSE)),IF($C3>$N$3,$O$3:$O$6,$L$3:$L$8))))


Table 1

1 A B
2 PLC Cd T/S Date
3 60 9/28/2012
4 61 8/2/2013
5 62 10/12/2012
6 63 8/30/2013
7 64 4/12/2013
8 65 8/30/2013


J M N O P Q R
PLC Rate 1 Starting Date Ending Date Rate 2 Starting Date Ending Date
60 44.94 9/25/2012 12/2/2012 45.04 12/3/2012 9/24/2013
61 62.57 9/25/2012 12/2/2012 62.67 12/3/2012 9/24/2013
62 47.8 9/25/2012 12/2/2012 47.9 12/3/2012 9/24/2013
63 93.86 9/25/2012 12/2/2012 93.96 12/3/2012 9/24/2013
64 67.51 12/3/2012 9/24/2013
65 71.75 12/3/2012 9/24/2013

 

[sophie619]

The end result should be the correct rate.

The first table gives the date range for the rate so i.e. on PLC Cd 60 if the date is >12/2/12 then it is 44.94, if the date is <12/3/12 then it is 45.04.

The second table is the Codes and Dates I need to vlook up the PLC code in table 1 to table 2, then use the if statement to determine the rate based on date.

Does that make sense?

I think so. Do you have any dates out of that 9/2012 - 9/2013 range? I guess what I'm asking is will you have to check both side of the range, or will it be sufficient to just compare the given date with the December cut off?

 

[Ninjababy1123]

December cut off.

 

[sophie619]

I took your reference table and put it on one sheet (called "RefSheet") in the upper left corner and put the other table on the next sheet (called "Data").

This is the data that I tested and the rates that the formula produced: A B C PLC Date Rate 60 9/28/2012 44.94 61 12/15/2012 62.67 62 10/8/2012 47.8 60 5/1/2013 45.04 <tbody> </tbody>

<tbody>
</tbody>

For cell C2 in the Data sheet, the formula was "=VLOOKUP(A2,RefSheet!$A$2:$G$5,IF(B2<refsheet!$d$2,2,5))"

For VLOOKUP, you only need to reference which column in your lookup table you want displayed (hence the 2 and 5 at the end of the IF statement). Obviously, I omitted the ERROR checking, but it looks like you are familiar with that enough to add it on your own.

Hope that helps.

ETA: You said the December cut-off is sufficient, so just keep in mind that this won't work for anything outside the range of Sept 2012-Sept 2013.</refsheet!$d$2,2,5))"

 

[Ninjababy1123]

Thanks for your help