Excel 2007 : vlookup used to populate specific cells with data

**NMStorm** · 04-08-2010, 04:53 PM

I’m very grateful to the member who helped answer my last question. I have another one.

Two cells

A1: a beginning date
A2: an ending date

I need cells a3 through a30 to populate a specific number that corresponds to the beginning date and any dates 14 days in between the beginning date and ending date.

Example:

A1: 01/01/2000 A2: 01/01/2001
A3: 1001 B3: 01/01/2000
A4: 1010 B4: 01/15/2000

The number in cell A3 will be from a vlookup and will correspond with the number on B3. The key is that each cell between a3:b40 needs to be auto-populated with numbers based on the input of a1, a2. If there are not enough numbers between cells a1:a2 to fill all forty cells, then it just stops at the last date (a2).

I hope I am being clear enough with this. I thought about attaching an .xlxs document but I am not able to do so from my current location.

**Paul** · 04-08-2010, 05:01 PM

If I understand what you're asking, you essentially want to fill dates in B3:B40 that are within the range of the dates in A1:A2, and the dates should be in 14-day increments?

If so, in B3 put

=A1

In B4 put

=IF(B3="","",IF(B3+14>$A$2,"",B3+14))

Fill down to B40. I assume you would then put your VLOOKUP formula in A3:A40.

**Richard Buttrey** · 04-08-2010, 05:03 PM

Hi,

It's not clear to me precisely what it is you want. When you are able, upload a workbook showing a before and after example.

Rgds

**NMStorm** · 04-08-2010, 06:35 PM

I know you guys don't like jpeg's uploaded instead of excel files but I really can't upload excel files to this site. If I tried, all you would see is that the content is blocked (stupid liquid machines software).

Attached is a jpeg. So the user would input the start date in G1 and end date in H1. Column A has all start dates and columb b has all end dates. Column's C and D has the data that would need to be populated into cells G3:H13 based on the input dates in G1 and H1.

I could do a vlookup to find the start date and populate the cells from there, but I would need to also find the end date to know where to stop. This would mean that I would need a lookup to look at both G1 and H1 and output based on both criteria not just one.

I hope this helps.

**Richard Buttrey** · 04-08-2010, 06:51 PM

Still not entirely clear. What are the results in G3:H12?

If you're trying to identify the values in A & B which are between the start and end dates, you could use a helper column with an

Please Login or Register  to view this content.

copied down. Then use Data Extract with a criteria of "Yes"to extract date from columns C&D with where the helper column = "Yes"

HTH

**NMStorm** · 04-09-2010, 10:02 AM

I was able to create an example of what I want and have attached it to this message.

Basically, it's going to be a lookup but with two criteria. The first date you enter will find the first date in the table and insert data associated with it in separate cells. The second input date is the end date and will find a second input date in the table. All associated data between (and including) those two dates will be populated in separate cells.

I appreciate any input.

**NBVC** · 04-09-2010, 10:34 AM

I can't attach a file.. not sure how you were able too

as we are having problems with those icons...

... anyways.

What I did was ensure there was a row above the database could be header title row...

Then in E2:

=IF(AND(A2>=$G$1,B2<=$H$1),COUNT(E$1:E1)+1,"")

copied down

in F2: =MAX(E:E)

Then in G2: =IF(ROWS($A$1:$A1)>$F$2,"",INDEX(C:C,MATCH(ROWS($A$1:$A1),$E:$E,0)))

copied down as far as you want and to next column.

**NMStorm** · 04-09-2010, 11:37 AM

Originally Posted by NBVC

I can't attach a file.. not sure how you were able too

as we are having problems with those icons...

... anyways.

What I did was ensure there was a row above the database could be header title row...

Then in E2:

=IF(AND(A2>=$G$1,B2<=$H$1),COUNT(E$1:E1)+1,"")

copied down

in F2: =MAX(E:E)

Then in G2: =IF(ROWS($A$1:$A1)>$F$2,"",INDEX(C:C,MATCH(ROWS($A$1:$A1),$E:$E,0)))

copied down as far as you want and to next column.

I don't know how I got it to attach a file. I was having the same problems but I kept going back and fourth assuming I was the problem when the "manage attachments" box finally just showed up. It looks like it is working now.

As for your solution. I am happy and impressed! It works. It brings the first column down which is the most important thing. I can easily get it to bring the second column down with it now. I really appreciate your help.