CODEWITHSUNDEEP
exceldateexcel-formulalookup
Matthew Perryman
Matthew Perryman

Reputation: 129

Having Excel return a date, based on a text based cell input

I have a self made workbook in excel for by work. On one sheet (Lets call it Sheet 1) I list our main suppliers, their address, how many items we have purchased and total spent.

Eg. Supplier 1, Address, #Items purchased, Total

On a seperate sheet (Sheet 2) in the same workbook I have a full order breakdown (Date, Supplier, Client, Amount, Items etc) for about 10 years worth of data (Approx 1000 rows of data).

Eg. Order Date, Client, Sales Order Number, Supplier, Item, Cost, etc.

In sheet 1, I want it to automatically display the date of the most recent order associated with that supplier based on the info from sheet 2. So it will look something like this:

Eg. Supplier 1. Address, #Items purchased, Total, (Date from recent order on sheet 2)

I think I need to use either a LOOKUP, or INDEX, or MATCH or a combination of all three.

All solutions I have found online deal with a range of dates, I want this to have one specific date shown associated with that specific supplier. Un fortunately I cannot provide pictures as the data is sensitive. Can anyone help?

Edit: I do not know if this will help but to calculate the cost of certain items in the workbook I have used this formula: =LOOKUP(MATCH(N2,Calcs!B$1:B$147,0),Calcs!A$1:A$147,Calcs!C$1:C$147)

Upvotes: 1

Views: 130

Answers (2)

James Scott
James Scott

Reputation: 1074

Have you tried the following array formula?

=MAX(IF($D$2:$D$7=$A2,$E$2:$E$7,0))

enter image description here

Note you can use Evaluate formula to see how it works, and you need to press ctrl+shift+enter after entering the formula to get the curly brackets at each end that change behaviour to an array forula.

Regards,

James

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

Based on the version of Excel you would use one of the following:

2007 or earlier this Array Formula:

=MAX(IF('Sheet2'!$D$1:$D$100=A2,'Sheet2'!$A$1:$A$100))

It is an Array so Ctrl-Shift-Enter when leaving edit mode.

2010 or later:

=Aggregate(14,6,'Sheet2'!$A$1:$A$100/('Sheet2'!$D$1:$D$100=A2),1)

If you have the most current Excel with office 365 or are using the online version then:

=MAXIFS('Sheet2'!$A$1:$A$100,'Sheet2'!$D$1:$D$100,A2)

Upvotes: 1

Related Questions