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

**lilsnoop** · 05-15-2007, 10:02 PM

I agree with what you are saying... It sounds though I'll have to set up my dates to be displayed; day, month & year, unlike what I had intended which would have been month, day, & year. Thanks again!

**lilsnoop** · 05-15-2007, 11:04 PM

Rylo,

using sheet 3 (Sam Hill) change the "step raise date" to 4/2/07 and then below in A6 change the date to 4/2/07, In the ID# put 4, you'll get the wage $55. If you then go to page four (Lloyd Smith), which now sheet 3 should be referencing, keep (or make) the "step raise date" to read 4/1/07, you'll see that my formula in Sheet 3 (Sam Hill) column C6 does the vlookup correct for the wage cells affiliated to id#4 but it is not analyzing the dates in F3 or G3 from sheet four but instead it is comparing them to F3 or G3 in Sheet 3, which isn't what I want. ID#4's (Lloyd Smith's) wages aren't suppose to be compared with Sam Hills step raise dates and/or raise dates. I sure hope this makes sense... and please know I do appreciate your time in looking at this..

and/or if this doesn't make sense.. just play with the dates in the "step raise date" in sheet 3 (Sam Hill sheet) and even if you have the ID#4 below, which should reflect the data for Lloyd Smith (dates in F3 & G3 included from Smith's Sheet), you'll see that the Step raise dates in sheet 3 will affect ID4 wages. This might be the easiest way to demonstrate what I'm referring to.

**rylo** · 05-16-2007, 06:23 PM

Hi

The formulas you are using are making comparisons on the current sheet, not on the sheet related to the ID in column B. You will have to use the indirect function on all parts of the formula if you want to relate to the sheet relevant to the ID. As an example

If Sam Hill!B4 = 4 then, looking at the formula in C6
IF(B6="","",INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&IF($A6>=$G$3,"'!E3",IF($A6>=$F$3,"'!D3","'!C3"))))
the part
IF($A6>=$G$3
will relate to the sheet sam hill. If you want this to refer to Lloyd Smith, then you will have to use the indirect on each cell reference along the lines of

IF(indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"!$A6")>=indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"!$G$3")

If this is not clear expand on the example you have given below to show what result you should see, and where that result should come from. Also put the dates in the form 2 May 2006 when explaining, as I default to a dd/mm/yy format, and I don't know if 2/4/07 is really 4 Feb or 2 April.

rylo

**lilsnoop** · 05-16-2007, 08:55 PM

I'm not very familiar with Vlookup function formulas.. If I understand what I believe you are telling me my formula in C6 should be something like this?
IF(indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"!$A6")>=indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"!$G$3"),"'!E3",IF(indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"!$A6")>=indirect(VLOOKUP(B6,Lookup!$A$2:$B$5,2)"!$F$3"),"'!D3","'!C3")

The A6 column (date) on the Sam Hill sheet will need to be compared with the Lloyd Smith sheet dates affiliated with G3 & F3, if the ID column indicates a 4, which identifies Lloyd Smith. I would need this formula to adapt to whatever sheet is reflected by the number in the ID column. The formula above didn't appear to work for me. I'm sure I've done something wrong and as always I appreciate any/all advice. I'm learning more and more from each tip you give... Thanks again!

**rylo** · 05-16-2007, 10:10 PM

Hi

See if
Sam Hill!C6: =IF(B6="","",INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&IF(INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"'!$A6")>=INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"'!$G$3"),"'!E3",IF(INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"'!$A6")>=INDIRECT("'"&VLOOKUP(B6,Lookup!$A$2:$B$5,2)&"'!$F$3"),"'!D3","'!C3"))))

with Sam Hill!B6: 4

gives you the correct result.

If not, can you put up a new sample file, with your various example data, and the expected result you would expect to see where, and why it should be that result.

Also, can you unlock the cells in rows 1:9 so I can change date formats if I need.

rylo

**lilsnoop** · 05-17-2007, 08:32 PM

I do have excel 2007, but I saved this in an older version not knowing what version you have.. Thanks for the time you've put in helping in this matter. It is much appreciated!

**lilsnoop** · 05-18-2007, 08:03 PM

I had to make an ever so slight variation to your formula and it works perfect!! Thanks so much for your help! This is what I had to change the formula to in case anyone else is needing something similar.

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

(note: this formula works in my 1st attachment not in the most recent one-cell numbers are slightly different...)