Vlookup = #N/A

**gnoke** · 08-24-2010, 09:06 AM

Hi,

I am using the following IF formula;

=IF(AND(I21>1,I21<2),VLOOKUP(A3+30,A21:G968,2,FALSE),"")

For most of the cells this work smoothly. However;
The lookupvalues in column A is dates. I do not have data for all the dates of the year, and therefore I get an error when the lookupdate + 30 days does not exist in my spreadsheet.

What I am trying to do, is get the "A3+30" in my formula to become some sort of count. That is to say, I would like the values in A33 to show, regardless if this is the date in A3 + 30 days.

Hope this explaination was good enough for someone to grasp my problem and are willing to guide me

(If not I can provide a sample sheet)

Gnoke

**romperstomper** · 08-24-2010, 09:08 AM

If you want to look up A33, why not lookup A33 instead of A3 + 30?
If that's not what you mean, then I think a sample workbook would help a lot!

**NBVC** · 08-24-2010, 09:09 AM

Do you mean?

=IF(AND(I21>1,I21<2),IF(ISNA(MATCH(A3+30,A21:A968,0)),A33,VLOOKUP(A3+30,A21:G968,2,FALSE)),"")

**gnoke** · 08-24-2010, 09:28 AM

Thanks for replays,

I was clearly not good enough to explain, my bad.

The cell D5 is a sample issue here. The rest #N/A is (as I you probably already understand) due to the limited date-sample here.
I see that A1 + 30 here looks after date 04.02.2007, since there is no such date in my sheet I would really like to get the value of the next date that exists, in this case the value of 05.02.2007 = 1.

Gnoke

**romperstomper** · 08-24-2010, 09:35 AM

Assuming you are only missing weekends, as appears to be the case, how about:
=IF(AND(C1>1,C1<2),VLOOKUP(A1+30+CHOOSE(WEEKDAY(A1+30,2),0,0,0,0,0,2,1),A1:C40,2,FALSE),"")