Help with VLOOKUP formula

[charliebe521]

I have a sheet1 (named MASTER) in a workbook with all relevant data filled in. I also have several sheets after that built exactly the same except no data. Row 1 is the column titles and row 2 begins the actual data. In column A, beginning with A2 I have a unique 4 digit ID number for each employee. Column B is employees last name, column C is employees first name, so on and so forth with data for each employee.

What I am trying to accomplish is this: In sheet2, sheet3, etc., I want to be able to just type in a unique ID number in column A and then all of the data from that row on the master sheet will auto populate into whichever sheet and row I typed the ID number.

Basically looks like this table.


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]id[/TD]
[TD]last[/TD]
[TD]first[/TD]
[TD]craft[/TD]
[TD]cell #[/TD]
[TD]s.s. #[/TD]
[TD]cert.date[/TD]
[TD]cert.date[/TD]
[TD]location[/TD]
[TD]start date[/TD]
[TD]cert.date[/TD]
[TD]cert.date[/TD]
[TD]cert.date[/TD]
[TD]cert.date[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]doe[/TD]
[TD]john[/TD]
[TD]fit[/TD]
[TD]111-111-1111[/TD]
[TD]999-99-9999[/TD]
[TD]1/1/15[/TD]
[TD]9/1/15[/TD]
[TD]LA[/TD]
[TD]1/1/15[/TD]
[TD]9/1/15[/TD]
[TD]1/1/15[/TD]
[TD]9/1/15[/TD]
[TD]1/1/15[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]doe[/TD]
[TD]jane[/TD]
[TD]for[/TD]
[TD]222-222-2222[/TD]
[TD]888-88-8888[/TD]
[TD]2/1/15[/TD]
[TD]8/1/15[/TD]
[TD]LA[/TD]
[TD]2/1/15[/TD]
[TD]8/1/15[/TD]
[TD]2/1/15[/TD]
[TD]8/1/15[/TD]
[TD]2/1/15[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]smith[/TD]
[TD]bob[/TD]
[TD]wld[/TD]
[TD]333-333-3333[/TD]
[TD]777-77-7777[/TD]
[TD]3/1/15[/TD]
[TD]7/1/15[/TD]
[TD]LA[/TD]
[TD]3/1/15[/TD]
[TD]7/1/15[/TD]
[TD]3/1/15[/TD]
[TD]7/1/15[/TD]
[TD]3/1/15[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]smith[/TD]
[TD]betty[/TD]
[TD]fit[/TD]
[TD]444-444-4444[/TD]
[TD]666-66-6666[/TD]
[TD]4/1/15[/TD]
[TD]6/1/15[/TD]
[TD]TX[/TD]
[TD]4/1/15[/TD]
[TD]6/1/15[/TD]
[TD]4/1/15[/TD]
[TD]6/1/15[/TD]
[TD]4/1/15[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]williams[/TD]
[TD]john[/TD]
[TD]wld[/TD]
[TD]555-555-5555[/TD]
[TD]555-55-5555[/TD]
[TD]5/1/15[/TD]
[TD]5/1/15[/TD]
[TD]TX[/TD]
[TD]5/1/15[/TD]
[TD]5/1/15[/TD]
[TD]5/1/15[/TD]
[TD]5/1/15[/TD]
[TD]5/1/15[/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD]williams[/TD]
[TD]jane[/TD]
[TD]wld[/TD]
[TD]666-666-6666[/TD]
[TD]444-44-4444[/TD]
[TD]6/1/15[/TD]
[TD]4/1/15[/TD]
[TD]LA[/TD]
[TD]6/1/15[/TD]
[TD]4/1/15[/TD]
[TD]6/1/15[/TD]
[TD]4/1/15[/TD]
[TD]6/1/15[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]johnson[/TD]
[TD]bob[/TD]
[TD]wld[/TD]
[TD]777-777-7777[/TD]
[TD]333-33-3333[/TD]
[TD]7/1/75[/TD]
[TD]3/1/15[/TD]
[TD]LA[/TD]
[TD]7/1/15[/TD]
[TD]3/1/15[/TD]
[TD]7/1/15[/TD]
[TD]3/1/15[/TD]
[TD]7/1/15[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]johnson[/TD]
[TD]betty[/TD]
[TD]qc[/TD]
[TD]888-888-8888[/TD]
[TD]222-22-2222[/TD]
[TD]8/1/15[/TD]
[TD]2/1/15[/TD]
[TD]TX[/TD]
[TD]8/1/15[/TD]
[TD]2/1/15[/TD]
[TD]8/1/15[/TD]
[TD]2/1/15[/TD]
[TD]8/1/15[/TD]
[/TR]
[TR]
[TD]1009[/TD]
[TD]jones[/TD]
[TD]john[/TD]
[TD]supt[/TD]
[TD]999-999-9999[/TD]
[TD]111-11-1111[/TD]
[TD]9/1/15[/TD]
[TD]1/1/15[/TD]
[TD]TX[/TD]
[TD]9/1/15[/TD]
[TD]1/1/15[/TD]
[TD]9/1/15[/TD]
[TD]1/1/15[/TD]
[TD]9/1/15[/TD]
[/TR]
</tbody>[/TABLE]

 

[ITWare2008]

Hi Calrlie,

I think this will work,


=index(Master!b2:n1000,match(employeeID, Master!a2:a1000,0),cols($c2:c2))

Vândalo

 

[charliebe521]

Thank you for your reply ITWare2008

Where exactly would I put this formula?

 

[ITWare2008]

Hi Charlie,

Place the formula on cell b2 of a worksheet that will lookup for data. Then copy and drag it to the other cols.
When you have one worksheet complete with those formulas copy it to the other worksheets.
I think - but didn't tested it - you could select all your worksheets that will lookup for data on master worksheet and write the formula and copy it as you do with a single worksheet

Vândalo

 

[charliebe521]

When I put the formula in B2 #NAME? comes up, and when I type in the ID number it does not change. It still shows #NAME?

 

[ITWare2008]

Charlie,

Check your master worksheet name and replace employeeID with cell address where the formula is being written. It should work now.


Vândalo

 

[charliebe521]

Now it shows #N/A (which I assume it would until I put in an ID number) but when I type in an ID number it changes to #NAME?

I new to this forum. Is there a way I can get you a sample spreadsheet?

 

[ITWare2008]

Hello Charlie

Here it is.

Charlie.xlsx - Speedy Share - upload your files here


Vândalo

 

[charliebe521]

Thank you ITWARE2008!

You nailed it!

The formula is working exactly how I need it to work. I will continue to manipulate the spreadsheet just to make sure, but so far so good!

 

[strooman]

Another approach. For the sake of clarity I used only one sheet but you should implement it to your needs.

[B2] =IFERROR(INDEX(B$7:B$15;SMALL(IF(ISNUMBER(MATCH($A$7:$A$15;$A$2;0));ROW($A$7:$A$15)-ROW($A$7)+1);ROWS($A$7:B7)));"")

Ctrl+Shift+Enter
Drag across to N2

[A2] Your ID number.