Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,560
Default vlookup with multiple values

Hello,

I have 2 worksheets. One with historical rents: it has 5 columns:

A B C D
E
Store # active rent Monthly Rent Rent Start date
Annual rent
1 500 active 1,734 5/9/2003
20,809
2 500 active 2,365 6/1/2003
28,376
3 500 active 2,512 6/1/2008
29,263
4 501 active 4,411 3/4/1998
52,936


The second spreadsheet is organized like an income stmt with monthly
financial data.

if i change the store # in cell D4, it will update the entire sheets income
stmt showing financial data for the entire sheet. i'm trying to show
historical rent, so it will show rent that was paid that month... currently
whenever i change the rent, from the sheet that has the current rent it will
change everything in the past, so i just want it to show the actual rent that
was paid.

Any help would be great!

Thanks!

David


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default vlookup with multiple values

It's not real clear what you're wanting to do. Can you rephrase it?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello,

I have 2 worksheets. One with historical rents: it has 5 columns:

A B C D
E
Store # active rent Monthly Rent Rent Start date
Annual rent
1 500 active 1,734 5/9/2003
20,809
2 500 active 2,365 6/1/2003
28,376
3 500 active 2,512 6/1/2008
29,263
4 501 active 4,411 3/4/1998
52,936


The second spreadsheet is organized like an income stmt with monthly
financial data.

if i change the store # in cell D4, it will update the entire sheets
income
stmt showing financial data for the entire sheet. i'm trying to show
historical rent, so it will show rent that was paid that month...
currently
whenever i change the rent, from the sheet that has the current rent it
will
change everything in the past, so i just want it to show the actual rent
that
was paid.

Any help would be great!

Thanks!

David




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,560
Default vlookup with multiple values

I'm trying to show what rent was paid in a specific month and year. So, if
rent was $1,000 in March of 2007 and it changes to $1,200 in March of 2008.
So, if i have a list of one store with changing rent, i want it to correspond
with the date.

I thought of a sumproduct or an index... Maybe, i shouldn't have used
vlookup as the subject.

Let me know if this helps or if you need more clarification.

Thanks!

~ David


"T. Valko" wrote:

It's not real clear what you're wanting to do. Can you rephrase it?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello,

I have 2 worksheets. One with historical rents: it has 5 columns:

A B C D
E
Store # active rent Monthly Rent Rent Start date
Annual rent
1 500 active 1,734 5/9/2003
20,809
2 500 active 2,365 6/1/2003
28,376
3 500 active 2,512 6/1/2008
29,263
4 501 active 4,411 3/4/1998
52,936


The second spreadsheet is organized like an income stmt with monthly
financial data.

if i change the store # in cell D4, it will update the entire sheets
income
stmt showing financial data for the entire sheet. i'm trying to show
historical rent, so it will show rent that was paid that month...
currently
whenever i change the rent, from the sheet that has the current rent it
will
change everything in the past, so i just want it to show the actual rent
that
was paid.

Any help would be great!

Thanks!

David





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default vlookup with multiple values

You can use SUMPRODUCT for that as long as there aren't multiple rents
listed for the same month/year.

Something like this:

=SUMPRODUCT(--(A2:A10=store_number),--(D2:D10=rent_date),C2:C10)

However, I'm guessing you want something different because the rent dates
have gaps. Do you really want the most recent rent?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I'm trying to show what rent was paid in a specific month and year. So, if
rent was $1,000 in March of 2007 and it changes to $1,200 in March of
2008.
So, if i have a list of one store with changing rent, i want it to
correspond
with the date.

I thought of a sumproduct or an index... Maybe, i shouldn't have used
vlookup as the subject.

Let me know if this helps or if you need more clarification.

Thanks!

~ David


"T. Valko" wrote:

It's not real clear what you're wanting to do. Can you rephrase it?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello,

I have 2 worksheets. One with historical rents: it has 5 columns:

A B C D
E
Store # active rent Monthly Rent Rent Start date
Annual rent
1 500 active 1,734 5/9/2003
20,809
2 500 active 2,365 6/1/2003
28,376
3 500 active 2,512 6/1/2008
29,263
4 501 active 4,411 3/4/1998
52,936


The second spreadsheet is organized like an income stmt with monthly
financial data.

if i change the store # in cell D4, it will update the entire sheets
income
stmt showing financial data for the entire sheet. i'm trying to show
historical rent, so it will show rent that was paid that month...
currently
whenever i change the rent, from the sheet that has the current rent it
will
change everything in the past, so i just want it to show the actual
rent
that
was paid.

Any help would be great!

Thanks!

David







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookup multiple values - sum returned values into single cell se7098 Excel Worksheet Functions 12 April 2nd 23 07:32 PM
Vlookup multiple criteria multiple occurrences sum values se7098 Excel Worksheet Functions 0 March 26th 09 08:31 PM
vlookup on multiple values samuel Excel Worksheet Functions 3 May 7th 08 04:01 PM
vlookup, multiple values, sum values into one cell?? Phillips L Excel Worksheet Functions 4 November 9th 05 01:31 AM
VLookup for multiple values!! navneetjn Excel Worksheet Functions 3 July 19th 05 07:43 PM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"