Multiple Vlookup

[Alfredo_CPA]

I have a big table which all the states. Please notice the total column is
not always the same and the data for the states are not always in the same
rows, some times there are "empty rows" between each state data. The states
have "labels" for the columns in pairs (Lets assume the table starts on
row1). Here is an example with two of the states. :

_________________________________________________
COLUMNA COLUMNB COLUMNC COLUMND COLUMND
Q1 Q2 Q3 Total
"empty row"
TEXAS 50 30 10 90
"empty row"
OKLAHOMA 10 15 5 35
"empty row"
"empty row"
Everything is bigger in Texas
Hello Oklahoma
"empty row"
"empty row"
"empty row"
"empty row"
Q1 Q2 Total
"empty row"
NEW YORK 20 35 55
"empty row"
CALIFORNIA 5 5 10
"empty row"
"empty row"
NY The big apple state
CA lets go to the beach
__________________________________________________
I have another table in another sheet with all the states listed in each row
starting in B5 and another information from column C to AY (the information
in columns C to AY is not relevant for my specific problem)
I need a formula in AZ5 - to be copied to the rows below (not sure if the
answer is a Vlookup) that works for all the states and gives me as a result
the total for the state

Your input is really appreciated

--

 

[Max]

Assuming the big table is in Sheet1, say within cols A to F, data from row1
down, where the key note is: > .. the "Total" column is not always the same
(statenames are listed in col A)

Presuming that the "Total" col is always the last, rightmost col
here's a way to extract it into a col to the right, say in col G?

Put in G1, array-enter (ie press CTRL+SHIFT+ENTER to confirm the formula)
=IF(MAX((B1:F1<>"")*(COLUMN(B1:F1)))<1,"",INDEX(B1:F1,,MAX((B1:F1<>"")*(COLUMN(B1:F1)))-1))
Copy G1 down as far as required

Then in the other sheet, where you have the state names in B5 down (eg:
Texas, etc), you could use SUMIF in AZ5, copied down:
=SUMIF(Sheet1!A:A,B5,Sheet1!G:G)
to gather the required "Total" figs for each state
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik