VLookup question

**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!