Vlookup w/ criteria from a total based on multiple condition

[floored]

I have an existing table (actually 50 different ones) I want to select data from. My columns headings are job numbers and row headings are states. Each job has 3 columns of data for it, although the job number only appears over the first columns.

I want my formula to pull the data in each job's third column into a new table where the column headings are the all the states, and the row headings are just certain jobs numbers which I have selected.

I've tried to nest vlookup/hlookup formulas to accomplish this, but having the data I want in a different column then the job number seems to be tripping me up. any thoughts?

Thanks

 

[IML]

I may be totally missing the boat, but...
I set up a table occupying A1:G10 on sheet 1. The first state is in A2, second A3, etc. Job number 1 occupies B1, Job 2 occupies E1. You could use
=INDEX(Sheet1!$A$1:$G$10,MATCH(A1,Sheet1!$A$1:$A$10,0),MATCH(A2,Sheet1!A1:G1,0)+2)
where on your new sheet A1 is state of interest and A2 is the job number of interest.

Hope that helps. If this doesn't, please list a little sample data (say 2 or 3 row covering 2 or 3 jobs).

 

[DonkeyOte]

Sounds like you need to use an offset to cater for the 3 columns per job.

An offset enables you to vary the column number simply - you will use +1 or +2 to pick up third column

eg LOOKUPTABLE for column number per job
JOB STARTING COLUMN
a 1
b 4

and you want third column of job "a" - you will want to lookup "a" in your offset table (that will give you column number 1) and then add 2 to it - to give 3 - make sense?

so vlookup(blah,blah,VLOOKUP("a",OFFSET,2,FALSE)+2,false)

 

[floored]

Sorry,

I guess I was unclear about my needs. The challenge is my source file is a pre-existing format which I cannot change. There are over 1,000 jobs which I need to perform this calculation on in over 50 files. While I can write the macro to do the repetitive part, I can't figure out how the calculation which needs to be repeated would be performed. Here is a sample of the data and anticpiated result.

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3	SOURCEDATA
4
5		0204-031-10			0206-020-10			0206-020-10
6	AK	1	10.0%	300	2	0.0%	100	9	0.0%	700
7	AL	4	40.0%	400	4	0.0%	200	1	0.0%	20
8	AR	5	50.0%	500	4	0.0%	50	-	0.0%	-
9
10
11	DESIREDRESULTS
12
13		AK	AL	AR
14	0204-031-10	300	400	500
15	0206-020-10	800	220	50
16
sheet1

This message was edited by floored on 2002-09-12 12:41

 

[IML]

Given your sample, try
=SUMIF($A$5:$J$5,$A14,OFFSET($C$5:$L$5,MATCH(B$13,$A$5:$A$8,0)-1,0))

in cell B14 and to copy to other cells.

Good Luck