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.

id

last

first

craft

cell #

s.s. #

cert.date

cert.date

location

start date

cert.date

cert.date

cert.date

cert.date

1001

doe

john

fit

111-111-1111

999-99-9999

1/1/15

9/1/15

LA

1/1/15

9/1/15

1/1/15

9/1/15

1/1/15

1002

doe

jane

for

222-222-2222

888-88-8888

2/1/15

8/1/15

LA

2/1/15

8/1/15

2/1/15

8/1/15

2/1/15

1003

smith

bob

wld

333-333-3333

777-77-7777

3/1/15

7/1/15

LA

3/1/15

7/1/15

3/1/15

7/1/15

3/1/15

1004

smith

betty

fit

444-444-4444

666-66-6666

4/1/15

6/1/15

TX

4/1/15

6/1/15

4/1/15

6/1/15

4/1/15

1005

williams

john

wld

555-555-5555

555-55-5555

5/1/15

5/1/15

TX

5/1/15

5/1/15

5/1/15

5/1/15

5/1/15

1006

williams

jane

wld

666-666-6666

444-44-4444

6/1/15

4/1/15

LA

6/1/15

4/1/15

6/1/15

4/1/15

6/1/15

1007

johnson

bob

wld

777-777-7777

333-33-3333

7/1/75

3/1/15

LA

7/1/15

3/1/15

7/1/15

3/1/15

7/1/15

1008

johnson

betty

qc

888-888-8888

222-22-2222

8/1/15

2/1/15

TX

8/1/15

2/1/15

8/1/15

2/1/15

8/1/15

1009

jones

john

supt

999-999-9999

111-11-1111

9/1/15

1/1/15

TX

9/1/15

1/1/15

9/1/15

1/1/15

9/1/15

<tbody>
</tbody>

 

[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.