Using VLOOKUP to Copy Data In Row Where Cell Below Name is Blank

[jrjobe]

Good Evening!

I am using VLOOKUP to pull data from another sheet called "001A Hrs Summary" that contains data pulled from an external source. The problem I am running into is if the row on the "001A Hrs Summary sheet" that matches the VLOOKUP contains a blank cell directly below the name being looked up, then there is additional data in the second row, that belongs to the cell above it, that I need to populate next to the primary data in columns "J - Q" of the "001A Teams" sheet.

On the 001A Hrs Summary sheet, I highlighted the blank cells, which represents an individuals secondary qualification on an aircraft. The primary is what is listed directly across from their name, whereas one individual's primary (1st qual) may be AC2 another's primary may be AC1, and only if they have a secondary qualification will the 2nd row populate on the data sheet with a blank cell below their name.

In the sample below, Doe, John J. has a secondary qual (rows 8/9 of 001A Hrs Summary). It is that row, I am trying to populate in columns J - Q of the 001A Teams. The caveat to this is that the Hrs Summary is constantly updated by another office and names are constantly added and removed and quals added, so the rows Doe, John J. may be in today, may change tomorrow which is why I had to use VLOOKUP.

Is there any way to get the secondary qual to populate? If there is an easier way by automatically restructuring the Hrs Summary sheet, to possibly another sheet that will move the secondary next to the end of the primary line, I am open to it. I have been researching this tirelessly for days to no ends, and below is the best I could come up with.

Also, here is a link to the excel file on SkyDrive that I stripped down for security reasons. Columns A - Q are typically hidden.

I really appreciate any help I can get. I come here quite often when doing little projects that I get stumped on and this site has been a time saver.

Thanks again!

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	S
Doe, John D.	Doe, John D.
Doe, John J.	Doe, John J.

<tbody>
[TD="align: center"]3[/TD]

[TD="bgcolor: #C5D9F1"]AC1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]185.1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]69[/TD]
[TD="bgcolor: #C5D9F1, align: right"]185.1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #C5D9F1"]AC2[/TD]
[TD="bgcolor: #C5D9F1, align: right"]1951.1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]793[/TD]
[TD="bgcolor: #C5D9F1, align: right"]1851.1[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #C5D9F1, align: right"]92.4[/TD]
[TD="bgcolor: #C5D9F1, align: right"]0[/TD]
[TD="bgcolor: #C5D9F1, align: right"]7.6[/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

</tbody>

001A Teams​

[TABLE="width: 686"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 678"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=IF(S3="","",S3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=IF($A3="","",VLOOKUP($A3,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=IF(S4="","",S4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=IF($A4="","",VLOOKUP($A4,'001A Hrs Summary'!$B$1:$J$402,COLUMN(),FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010

	A	B	C	D	E	F	G	H	I	J
DIV	Name	A/C	Tot. Time	flights	Primary	Secondary	Instructor	Evaluator	Other
001A	Doe, John D.	AC1
Total Hours
001A	Doe, John A.	AC2
Total Hours
001A	Doe, John W.	AC2
Total Hours
001A	Doe, John J.	AC2
001A	AC1
Total Hours
001A	Doe, John Z.	AC1
001A	AC2
Total Hours
001A	Doe, John C.	AC1
Total Hours
001A	Doe, Johnny C.	AC1
001A	AC2
Total Hours
001A	Doe, John H.	AC2
Total Hours

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]185.1[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]185.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]185.1[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]185.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]303.6[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]303.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]303.6[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]303.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]360.2[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]360.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]360.2[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]360.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1951.1[/TD]
[TD="align: right"]793[/TD]
[TD="align: right"]1851.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]92.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.6[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: right"]964[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]961[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2915.1[/TD]
[TD="align: right"]1126[/TD]
[TD="align: right"]2812.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]92.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.6[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1069.5[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]984[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]76.7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1.8[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: right"]708.2[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]700.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.3[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1777.7[/TD]
[TD="align: right"]671[/TD]
[TD="align: right"]1684.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]1.8[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]423.6[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]423.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]423.6[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]423.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]926.7[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]925.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: right"]115.1[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]115.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1041.8[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"]1040.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]521.6[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]521.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]521.6[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]521.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

001A Hrs Summary​

 

[Peter_SSs]

Your use of COLUMN() in your existing formulas is risky. Say you decide to insert an extra column at the left of the '001A Teams' sheet, all your formulas will return incorrect results. Try it in a copy of your workbook. So as well as suggesting how you can get the second row of data, I'm also suggesting a more robust way to get the first row.

It would be possible to do this task with formulas in columns B:Q only, but those formulas for the second row of data (columns J:Q) would be pretty complex.
Instead, I'm suggesting using two helper columns in the '0011A Teams' sheet as shown. I've used columns T:U but they could be any available columns and they could be hidden if you want after populating the formulas. Using the helper columns keeps all the formulas pretty simple.

Formula in B3 is copied across to column I and then down.
Formula in J3 is copied across to column Q and then down.
Formulas in T3 and U3 are just copied down.

Obviously you would have to change the $20 references in my formulas to cover all the rows of data in the '001A Hrs Summary' sheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		A/C	Tot. Time	flights	Primary	Secondary	Instructor	Evaluator	Other	A/C	Tot. Time	flights	Primary	Secondary	Instructor	Evaluator	Other			Row 1	Row 2
2
3	Doe, John D.	AC1	185.1	69	185.1	0	0	0	0										Doe, John D.	1
4	Doe, John A.	AC2	303.6	117	303.6	0	0	0	0										Doe, John A.	3
5	Doe, John W.	AC2	360.2	135	360.2	0	0	0	0										Doe, John W.	5
6	Doe, John J.	AC2	1951.1	793	1851.1	0	92.4	0	7.6	AC1	360.2	135	360.2	0	0	0	0		Doe, John J.	7	8
7	Doe, John Z.	AC1	1069.5	385	984	0	76.7	7	1.8	AC2	360.2	135	360.2	0	0	0	0		Doe, John Z.	10	11
8	Doe, John C.	AC1	423.6	141	423.6	0	0	0	0										Doe, John C.	13
9	Doe, Johnny C.	AC1	926.7	303	925.5	0	0	0	1.2	AC2	964	333	961	0	0	0	3		Doe, Johnny C.	15	16
10	Doe, John H.	AC2	521.6	215	521.6	0	0	0	0										Doe, John H.	18
11
001A Teams

 

[jrjobe]

Peter,

Thank you very much for your help! It works the way I have been trying to do for a while now. The only reason I was using COLUMN() was because I knew once the data was in there, there would be no need to add any columns to the left of the sheet and those columns would be locked and hidden from others. Any columns that would be added, are added to the right of column "Q". Columns B:Q are only being used so I could get the external data to be used elsewhere in the sheet. Using INDEX() is so much better - now that I understand how it's used. I thought of using a helper column but I couldn't figure out to do it.

If there was or is a way to contribute to you, I will. You are awesome and have saved me and my colleagues a lot of time.


Thanks again, Josh

 

[Peter_SSs]

Glad to help

If there was or is a way to contribute to you, I will.

That is not only not required (we help here because we want to) it is expressly forbidden - see here and Forum Rules (#8)

 

[jrjobe]

Thank you Sir! I should have restated it in a different way to show my gratitude. I have been trying to figure this out for a couple months. I sat down a few days ago to take another stab at it, and got to where I did before finally asking for help. I just wanted to express how thankful I am for your help. Definitely wasn't meaning to break board rules.


Josh

 

[Peter_SSs]

Thank you Sir! I should have restated it in a different way to show my gratitude. I have been trying to figure this out for a couple months. I sat down a few days ago to take another stab at it, and got to where I did before finally asking for help. I just wanted to express how thankful I am for your help. Definitely wasn't meaning to break board rules.


Josh

No problem, sorry if I misinterpreted your previous post.
Your thanks in a post like you have done is all that is required in this forum.