CODEWITHSUNDEEP
excelexcel-formula
celsomtrindade
celsomtrindade

Reputation: 4671

Function to return value based on date

I'm trying to make a "Cash Flow" System, but instead of filling each cell manually, i want to pickup a value from other tab based on the dead line of an invoice i have.

For example: In TAB 01 i have a detailed version of my invoices, with date, name, itens, values, etc. In TAB 02 i have my cash flow, wich is going to be auto-filled with the values of the invoices based on the dates.

I've been trying to do this with the INDEX and MATCH function, but my result is kind of weird. This print screen is what i have so far: PS.: the Date format in my country is DD/MM/YYYY.

enter image description here

What i want is to show the C14 cell in the C3 cell. Or, for example, if my date on C15 is 06/05/2014, i want to show the C14 cell on the F3 cell.

Can anyone, please, help me?

Upvotes: 1

Views: 5131

Answers (1)

teylyn
teylyn

Reputation: 35915

As I understand your question, you want to display the value of cell C14 in row 2, and below the cell that has the same date as cell C15.

The formula for that would be in cell A2

=IF($C$15=A1,$C$14,"")

Copy across.

I'm not sure that this is really what you want to achieve though. With this method you can watch only one date/value pair for a value.

You may want to step back a bit and explain the bigger picture. It looks as if the data architecture may need some improvement.

With regards to your use of the Index/Match:

  • The index is on the first row, so a value from the first row will be returned, which is a date. Hence the big number returned by the formula (formatted as currency, not date)
  • The Match function is all wrong. The second parameter must be a range, not a single cell. If you want to return an exact match, the last parameter must be a 0, not a 1

Upvotes: 1

Related Questions