CODEWITHSUNDEEP
exceldateexcel-formula
Oline
Oline

Reputation: 33

Formula to get the month of the last value

I have sales data by customer as follows:

| - | A             | B      | C      | D      | E      | F      | G      |
|---|---------------|--------|--------|--------|--------|--------|--------|
| 1 | Customer Name | Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | Jun-18 |
| 2 | Mr.A          | 1000   | 500    | 0      | 200    | 0      | 0      |
| 3 | Mr.B          | 0      | 300    | 200    | 0      | 0      | 100    |

I need the formula to know the last sales of the respective customer booked (the name of the month)

in this case, Mr. A last order is in Apr-18 while Mr.B is in Jun-18.

I have 2,000 plus customer and sales data since Apr 2016 up to last month, it will be a huge time saving to have a formula to help.

Upvotes: 3

Views: 225

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

An alternative to using LOOKUP() as in this answer, not sure what impact it has performance-wise as both need to create an array but I would take a stab in the dark that this is less performant:

=INDEX($B$1:$G$1,,MAX((B2:G2<>0)*COLUMN(B2:G2)-1)) - Ctrl+Shift+Enter

Ofcourse this could be edited to a dual lookup on the customer too:

=INDEX($B$1:$G$1,,MAX(INDEX(($B$2:$G$3<>0)*COLUMN($B$2:$G$3)-1,MATCH("Mr.B",$A$2:$A$3,0),0)))

This doesn't require the CSE as INDEX() handles the array manipulation

Upvotes: 0

pnuts
pnuts

Reputation: 59450

Assuming your 'months' are dates, not Text. Courtesy @barry houdini:

=LOOKUP(2,1/(B2:G2<>0),B$1:G$1)

in Row2 and copied down to suit, formatted mmm-yy.

Ref

Upvotes: 4

Related Questions