Vlookup w/ criteria from a total based on multiple condition

floored

New Member
Joined
Jun 26, 2002
Messages
16
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Jun 26, 2002
Messages
16
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
ABCDEFGHIJ
1
2
3SOURCEDATA
4
50204-031-100206-020-100206-020-10
6AK110.0%30020.0%10090.0%700
7AL440.0%40040.0%20010.0%20
8AR550.0%50040.0%50-0.0%-
9
10
11DESIREDRESULTS
12
13AKALAR
140204-031-10300400500
150206-020-1080022050
16
sheet1

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

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,265
Messages
5,577,094
Members
412,766
Latest member
BigRusty82
Top