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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,625
Messages
5,832,748
Members
430,163
Latest member
YesImAk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top