Adapting a vlookup function when look up cells not arranged in a table

**anna57** · 12-03-2009, 05:22 AM

Hello,

I have been using a vlookup function that was previously suggested to me on the forum (it is below). It was being used to look at whether a date was within certain quarters that had been specified in a table (BA1:BB8). However, I would like to change it so it just looked for whether a date was inbetween date values in 2 cells (C1 and G1). Does anybody know how I could adapt the formula?

=VLOOKUP(D3,$BA$1:$BB$8,2,TRUE)

When I look on help it says that this part of the formula is for "table array", does this mean that the look up values have to be in a table (i.e. as the different quarters were before) or can i specify 2 different cells (C1 and G1) to look between?

Thanks so much,

Anna

**DonkeyOte** · 12-03-2009, 05:31 AM

If just referencing two cells (non contiguous) use an IF

=IF(AND(D3>=C1,D3<=G1),"Yes","No")

**anna57** · 12-03-2009, 05:41 AM

Thanks for your speedy reply! The formula works and I have copied it down a column but where there are blank cells (e.g. if D4 is blank) it returns "yes" when I want it to say "no". Do you know of anyway I can adapt the formula?

Anna

**DonkeyOte** · 12-03-2009, 05:43 AM

Are C1 and G1 to remain absolute ?

=IF(AND($D3>=$C$1,$D3<=$G$1),"Yes","No")

I say this because were D blank I would still not expect a Yes return if the dates to which it was being compared (C1/G1) were not themselves blank - this is because though D < end Date it would not be > start date.

**anna57** · 12-03-2009, 05:51 AM

The cells c1 and g1 always have dates in them, it is the D cells that occasionally don't. I was surprised as well because I would have thought that they would come up as yes. I think that the database is treating the blank cells as if they were the value "00.1.00" as this has come up in another column that i have linked to the D column on another sheet (i.e. on another sheet the formulas in another column are ="maindatabase!"D1 etc.)

Does this shed any light on the issue?

Anna

**DonkeyOte** · 12-03-2009, 05:57 AM

Does this shed any light on the issue?

Not really.

Did you use the absolute references as advised ?

To reiterate - in the formula where D is blank and is returning "yes" then if that formula is referencing C1 & G1 something odd occurring

I very much suspect however that the references to C1 & G1 were not fixed at point of copy such that as the formula was copied down so those references adapted and thus the formula is in fact referencing blank cells in C & G.