I've been working on updating a spreadsheet off and on for the last two
months and can't get my VLOOKUP to work properly. Last month I asked for
help here and got a little direction, but the problem isn't fully resolved
and I'm about to give up completely.

My columns are laid out like this:

Company Name|City|State|Req.State|Sep08|Oct08|Nov08|Dec08| YTD Total

Each row is for a different company, though there may be three rows with
the same company name and different City, State, and/or Req.States -like
this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Tulsa OK TX 200
Jones Harbor LLC Tulsa OK TX
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

But I need it to look like this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100 100
Jones Harbor LLC Tulsa OK TX 200
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

I need to VLOOKUP the Company AND City AND State AND Req.State and if they
match, pull the revenue up to the same row apprpriate monthly column. Is
this possible?

Thanks so much for your help on this. It will be a true timesaver if I can
get this to work properly.

Debbi

Assuming original data is in Sheet1, new table is in Sheet2
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2: $B$100=$B2)*(Sheet1!$C$2:$C$100=$C2)*(Sheet1!$D$2: $D$100=$D2)*(Sheet1!$E$2:$E$100))

This also assumes that your months are both aligned the same.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"blucajun" wrote:

I've been working on updating a spreadsheet off and on for the last two
months and can't get my VLOOKUP to work properly. Last month I asked for
help here and got a little direction, but the problem isn't fully resolved
and I'm about to give up completely.

My columns are laid out like this:

Company Name|City|State|Req.State|Sep08|Oct08|Nov08|Dec08| YTD Total

Each row is for a different company, though there may be three rows with
the same company name and different City, State, and/or Req.States -like
this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Tulsa OK TX 200
Jones Harbor LLC Tulsa OK TX
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

But I need it to look like this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100 100
Jones Harbor LLC Tulsa OK TX 200
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

I need to VLOOKUP the Company AND City AND State AND Req.State and if they
match, pull the revenue up to the same row apprpriate monthly column. Is
this possible?

Thanks so much for your help on this. It will be a true timesaver if I can
get this to work properly.

Debbi

Thanks but could you elaborate on this a littlel more. I assume this goes in
Sheet 1 somewhere but where exactly (in the column for the newest month that
I'm trying to merge/incorporate)? Also, can you give me a breakdown of what
this code is doing? SUMPRODUCT tells me that it's summing, but I don't want
to sum, I want to merge (for lack of a better word) by line. Please explain
in laymans terms as I am not an advanced Excel user and am trying to learn
all this so I can use it in other applications. Thanks in advance!!


"Luke M" wrote:

Assuming original data is in Sheet1, new table is in Sheet2
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2: $B$100=$B2)*(Sheet1!$C$2:$C$100=$C2)*(Sheet1!$D$2: $D$100=$D2)*(Sheet1!$E$2:$E$100))

This also assumes that your months are both aligned the same.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"blucajun" wrote:

I've been working on updating a spreadsheet off and on for the last two
months and can't get my VLOOKUP to work properly. Last month I asked for
help here and got a little direction, but the problem isn't fully resolved
and I'm about to give up completely.

My columns are laid out like this:

Company Name|City|State|Req.State|Sep08|Oct08|Nov08|Dec08| YTD Total

Each row is for a different company, though there may be three rows with
the same company name and different City, State, and/or Req.States -like
this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Jackson MS MS 100
Jones Harbor LLC Tulsa OK TX 200
Jones Harbor LLC Tulsa OK TX
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA KY 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

But I need it to look like this:

Company City State Req. St. Sep Oct
Nov
Jones Harbor LLC Jackson MS MS 100 100
Jones Harbor LLC Tulsa OK TX 200
200
Jones Harbor LLC Tulsa OK MS
300
English Brakes Inc. Austin TX TX 100
English Brakes Inc. Atlanta GA KY 100 100
English Brakes Inc. Atlanta GA WY
175
English Brakes Inc. Houston TX TX 500

I need to VLOOKUP the Company AND City AND State AND Req.State and if they
match, pull the revenue up to the same row apprpriate monthly column. Is
this possible?

Thanks so much for your help on this. It will be a true timesaver if I can
get this to work properly.

Debbi

Excel 2007
Pivot Table
No formulas or code used:
http://www.mediafire.com/file/zwnmlt...12_10_08c.xlsx