Lookup

[skuddyb]

Hey Guys,

I'm trying to split some rows in excel based on what category their expenditure falls into. I have Col 1 with a list of job numbers and Col 2, 3 are different types of expense such as artwork, creative, data, and so on.

What I want to do is use a function that will let me split the jobs into what type of cost they were. For example if one job is creative and one job is equipment I wish to have tables with them all listed in. There are hundreds of jobs and I don't want to sift through them all.

I have tried to use VLOOKUP but can't seem to get it to work. Anyone able to provide some life saving answers?

 

[Dendro]

Could you give an example of your data in a table?

 

[skuddyb]

Could you give an example of your data in a table?

Not as such due to the the data itself.

Col1 - Job Numbers
Col 2 - Creative Costs
Col 4 - Artwork Costs
Col 6 - Mgmt Costs
etc

What I need to do is associate job numbers to the area that their cost falls into. When I finish I need a lists of Job Number - Area of Cost - Cost.

Say job number xxxx has a creative cost of 10,000 and job number xxxy has an artwork cost of 2,000 I need to be able to pull the job number and put it next to the corresponding cost then drag all the way down to populate the list. If a cost is 0 i need it to return FALSE or no Cost.

 

[Dendro]

invent some data for like 5 rows

 

[skuddyb]

invent some data for like 5 rows

Job Number	Creative	Artwork	Mgmt
1234	10,000	0	0
2345	0	3,000	2,000
3456	6,000	2,000	0
4567	0	0	4,000
5678	0	5,000	0

<tbody>
</tbody>

I used VLOOKUP with my array INVOICE_NO but it won't let me drag down and complete for the rest of the job numbers.

On a separate worksheet I have all the job numbers in Col1 and I wish to make as many tables as there is Cost Areas in order to analyse each one further.

Thanks for your help.

 

[Dendro]

Ok, so now you want out of this a table with Creative-jobs, another with artwork-jobs and another with management-jobs? Like this?

Creative jobs-id	Cost
1234	10,000
3456	6,000

<tbody>
</tbody>

 

[skuddyb]

Yes please. I have managed to Get VLOOKUP to work then used pivot tables from there. Is there a more efficient way of doing it?

 

[Dendro]

filter table on the column you want and remove 0 costs

 

[skuddyb]

That's what I did, cheers man