Pulling data

[Guest]

I am not sure if this question belongs to this discussion group but here it is

I have one sheet that contains all employee information
I would like to be able to type any employee ID in a seperate sheet in the
same workbook an pull all the related information listed in sheet 1

thanks

ce

 

[PY & Associates]

Try using VLookUp function please

 

[Guest]

Thanks

I was hoping I would not have to do a VLOOKUP in all columns and rows.

 

[Guest]

We could use either VLOOKUP or INDEX / MATCH (which is generally more
versatile than VLOOKUP)

Assume source data in Sheet1, cols A to E, data from row2 down. Col A = Emp
ids (I'll presume that the Emp ids are text numbers in 6 digits format,
possibly with leading zeros - as typical of data taken from HR host sys),
with cols B to E containing associated data

In Sheet2,

Let's assume you'd be entering the emp ids as numbers in A2 down

Using VLOOKUP

Put in B2
=IF(ISNA(MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)),"",VLOOKUP(TEXT($A2,"000000"),Sheet1!$A:$E,COLUMN(A1)+1,0))

Copy B2 to E2, fill down as far as required. Cols B to E will return the
required results from Sheet1.

Alternatively, using INDEX / MATCH (you could try this in another sheet) ..

Put in B2
=IF(ISNA(MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)),"",INDEX(Sheet1!B:B,MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)))

Copy B2 to E2, fill down as far as required. Cols B to E will return the
required results from Sheet1.

For more info, try Debra's nice coverage on VLOOKUP or INDEX/MATCH at her:

http://www.contextures.com/xlFunctions02.html
VLOOKUP

http://www.contextures.com/xlFunctions03.html
INDEX/MATCH

There's also some sample workbooks available for d/l & study

 

[Max]

I was hoping I would not have to do a VLOOKUP in all columns and rows.

See also my response in the other branch, where I've provided an example
using both VLOOKUP and INDEX/MATCH.

We could always use an incrementer (eg: COLUMN(A1)+1) for the column index
part in the VLOOKUP. Then we could just copy the formula straight across as
far as required to pull in all the columns from the source sheet.

Another alternative is to use INDEX / MATCH with the INDEX part of it (ie
the col to be returned) left relative, so that it increments when we copy
across.

---