Pulling data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Curtis - 30 Oct 2006 02:07 GMT

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

Reply to this Message

PY & Associates - 30 Oct 2006 02:32 GMT

Try using VLookUp function please

> I am not sure if this question belongs to this discussion group but here it is
>
[quoted text clipped - 5 lines]
>
> ce

Reply to this Message

Curtis - 30 Oct 2006 03:09 GMT

Thanks

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

> Try using VLookUp function please
>
[quoted text clipped - 8 lines]
> >
> > ce

Reply to this Message

Max - 30 Oct 2006 04:11 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 30 Oct 2006 03:26 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I am not sure if this question belongs to this discussion group but here it is
>
[quoted text clipped - 5 lines]
>
> ce

Reply to this Message