VLOOKUP???

[darkstar0]

I am new to working in Excel and I am trying to come up with a format that will allow entries of all workers' and their hours on a main sheet, which can then be viewed under a sheet specific to them. There should only be 2 columns of data to search. One of the employee, and the next of hours worked. I have tried using a VLOOKUP formula, but I am getting #N/A or repeated data entries. I have tried reading allot of the posts here for an answer, but they are beyond this level. I know this is probably elementary, but I'm stumped. Please set me on the right track here.

Thanks in advanced.

 

[P Sitaram]

Welcome to the Board!

Can you give more details of your data and the formula that you are using? It will also be useful if you post a part of the w/s using Colo's utility.

 

[darkstar0]

This is a look at what I am getting. Tammy only has two entries, but shows 6 on her sheet followed by #N/A. The formula I am using is:
=VLOOKUP("tammy",Sheet1!A2:C130,2,FALSE)

Worksheet 1Employee Hours
Tammy 6
Joe 5.5
Evelyn 6
Tanya 3.5
Adam 4
Tammy 2
Ethan 7

Sheet 2 "Tammy's
Tammy
6
2
2
2
2
2
#N/A
#N/A
#N/A


I appreciate it.

 

[P Sitaram]

I think in copying the formula down the rows the reference to the source range would have changed relatively. Hence, the above results.

In any case, the approach you are using will not serve your objective. If i understand you correctly, you want to extract all the rows pertaining to each employee to a separate worksheet. In that case, try filtering the source data (Data | Filter) and copying the filtered data to the relevant sheet.

 

[fairwinds]

Hi,

I think you need something like this:

This formula must be entered with Ctrl + Shift + Enter.

Book1
	A	B	C	D
1	Employee	Hours		Tammy
2	Tammy	6		6
3	Joe	5.5		2
4	Evelyn	6
5	Tanya	3.5
6	Adam	4
7	Tammy	2
8	Ethan	7
Sheet2

 

[Aladin Akyurek]

See:

http://216.92.17.166/board2/viewtopic.php?t=73376

for a less expensive system for returning a list of multiple values associated with a lookup value.

 

[Fergus]

Hi, An alternate solution which is simpler but needs a two stage approach would be to enter the formula: "=IF(ISNA(VLOOKUP("Tammy",Sheet1!A2:C2,2,FALSE)),"",VLOOKUP("Tammy",Sheet1!A2:C2,2,FALSE))" (without the quotes) into cell A2 of Tammy's sheet (Sheet2) and fill down for the same number of rows as you have in the composite sheet1- which looks as though it should be to row 130 from your initial formula. This will effectively reduce the array you are searching to each line on Sheet1 and will return Tammy's hours on the same line in Sheet2 in which they appeared on the original composite Sheet1. Then, second stage, highlight column A on Tammy's sheet and sort (Go to Data, Sort, click to include header row and sort in ascending order).