Date/Lookup/Reference Help

speth

New Member
Joined
Feb 18, 2013
Messages
27
I have a calc sheet with several items, but for brevity's sake, I have dates for the entire year listed down Column A, and values corresponding down Column B.

I'm trying to call up a reference from another cell, say C1, that will say either "given XXX date, the Column B value is YYY", where I can input the date that's being searched for in C2, or I can have it reference NOW().

I'm at a loss for this one, not even sure what angle to approach it from. Any suggestions?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Do you want to type a date in C1, and get the corresponding data from column B to appear in say D1?

try =INDEX(B:B,MATCH(C1,A:A,0)) in D1
 
Upvote 0
Yes, this works well and I can change the date as I go on.

Is there a way to have NOW() factored into it so that it updates automatically? I think NOW() features more than just days, months, and years, though...
 
Upvote 0
Yes, this works well and I can change the date as I go on.

Is there a way to have NOW() factored into it so that it updates automatically? I think NOW() features more than just days, months, and years, though...
NOW includes the time when the function calculates. Try TODAY() instead (date only).
 
Upvote 0
PERFECT, thank you both very much!

I've never used either index or match before, I'll have to experiment with them to become more familiar.
 
Upvote 0
Hi,
I've a problem that i need to find last issued date with in some issued date like below table:

Items Issue Date
Pen 06/01/2014
pencil 06/01/2014
Paper 06/01/2014
Pen 06/02/2014
pencil 06/03/2014
Paper 06/08/2014
Pen 06/11/2014
pencil 06/21/2014
Paper 06/21/2014
Pen 06/24/2014
pencil 06/26/2014



I need last issued date by using Excel formula like below:

Items Last Issue Date
Paper 06/21/2014
Pen 06/24/2014
pencil 06/26/2014

Pleas help me
 
Upvote 0
Hi,
I've a problem that i need to find last issued date with in some issued date like below table:

Items Issue Date
Pen 06/01/2014
pencil 06/01/2014
Paper 06/01/2014
Pen 06/02/2014
pencil 06/03/2014
Paper 06/08/2014
Pen 06/11/2014
pencil 06/21/2014
Paper 06/21/2014
Pen 06/24/2014
pencil 06/26/2014



I need last issued date by using Excel formula like below:

Items Last Issue Date
Paper 06/21/2014
Pen 06/24/2014
pencil 06/26/2014

Pleas help me
Note using an array formula (ctrl+ shift+enter). Copy E2 down and format col E cells as dates.
Excel Workbook
ABCDE
1ItemsIssue DateItemLast Date
2Pen6/1/2014Pen6/24/2014
3pencil6/1/2014Paper6/21/2014
4Paper6/1/2014Pencil6/26/2014
5Pen6/2/2014
6pencil6/3/2014
7Paper6/8/2014
8Pen6/11/2014
9pencil6/21/2014
10Paper6/21/2014
11Pen6/24/2014
12pencil6/26/2014
Sheet2
 
Upvote 0
Thank u so much for your quick reply, i try to use it but i can't do it correctly in my Excel-2007, i do not know how to use array formula , i try CTRL+SHIFT+ENTER! , but i can't success yet.
 
Upvote 0
Thank u so much for your quick reply, i try to use it but i can't do it correctly in my Excel-2007, i do not know how to use array formula , i try CTRL+SHIFT+ENTER! , but i can't success yet.
In E2 enter the formula I posted without the curly braces ({}) then hold down the shift and ctrl keys and press Enter - you should now see that Excel has added the curly braces. Copy that formula down col E to cover all the Items in col D.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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