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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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).
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,708
Members
450,635
Latest member
Rookie3510

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
Back
Top