VLookup question

**lilsnoop** · 05-14-2007, 08:30 PM

To anyone who can help... I'm using the following formula

=IF(B7="","",INDIRECT("'"&VLOOKUP(B7,lookup!$A$2:$ B$32,2)&IF($A7>=$H$3,"'!E3",IF($A7>=$G$3,"'!D3","' !C3"))))

For whatever reason, the last IF formula doesn't seem to work. A7 represents a date and G3 represents a date to compare with A7. In a sense I want this formula to be able to go to a worksheet via the VLookup function and then compare the two dates in H3 & G3 from that particular sheet with that of another sheet, specifically a date in A7. Everything appears to be working except for the later IF formula portion...It wants to always keep D3 even though the date in A7 may be less than D3. I've attached a zipped file of a smaller version of what i'm trying to accomplish with comments inserted as well. The cells won't exactly match my formula above, but it will be a similar formula. I'd sure appreciate anyone's help in this matter. Thanks in advance!

**rylo** · 05-14-2007, 09:45 PM

Hi

Did a quick test on a couple of cells in the sample file and it seemed to be working properly. John Doe!C6 returned !C3 as an example.

Can you give a reference on your example file where it is not working as you expect...

rylo

**lilsnoop** · 05-14-2007, 10:13 PM

In sheet "Sam Hill" type 2/12/07 in the A column, 4 in the ID column and you should see the $50 wage pop up automatically. The wage should have been $55 per ID#4 (Lloyd Smith's) page, specifically due to the "Step Raise" date specified in the F3 column. I need the individual sheets F3 & G3 dates per each individual to be compared with the column "A" of the particular sheet I'm working on (or rather the individual's sheet that called in sick. I hope that makes sense. I believe my worksheet is only comparing the wages for each individual (per their own sheet)with that of the dates reflected in F3 & G3 in the particular sheet I'm in and not their own F3 & G3. I'd sure like to know how to fix this.... I thought it was working too briefly... Thanks for any/all input

**rylo** · 05-14-2007, 11:29 PM

Hi

Sam Hill!A6: 2/12/07, Sam Hill!B6: 4, Sam Hill!C6 is 55

As 12/2/07 < G3 (6/1/08) and > F3 (1/4/07) then it should return !D3 which is what it does. Lloyd Smith!D3 is 55.

Where is that going wrong?

rylo

**lilsnoop** · 05-14-2007, 11:55 PM

The date column "A" in Sam Hill sheet would normally be typed month, day, year, i.e. 2/12/07 representing February 12th, 2007. If I type 12/2/07 it comes out $55, but entering dates like that would get real confusing for me and I'd probably mess up the data not being to familar with that date setup... So when I type 2/12/07 under a formatted column for dates (2/12/07) I get the darn $50..

**rylo** · 05-15-2007, 06:21 PM

HI

From what I see my comment stands.

As 12/2/07 < G3 (6/1/08) and > F3 (1/4/07)

2 Feb 07 is < 1 June 08 and
2 Feb 07 is > 4 Jan 07.

If these are not the dates being used, then perhaps check that your date formats are the same.

rylo