SOLVED

Get latest value from table based on multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2319267%22%20slang%3D%22en-US%22%3EGet%20latest%20value%20from%20table%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319267%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3EThis%20one%20is%20driving%20me%20mad.%3C%2FP%3E%3CP%3ECan%20anyone%20suggest%20a%20formula%20that%20will%20always%20get%20the%20latest%20value%20(and%20ignore%20blank%20values)%20from%20a%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWeek%20-----Period%20------Value%3CBR%20%2F%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-%C2%A3377%3CBR%20%2F%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%C2%A3219%3CBR%20%2F%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%C2%A3759%3CBR%20%2F%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-%C2%A31%2C106%3CBR%20%2F%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%C2%A3302%3CBR%20%2F%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%C2%A3409%3CBR%20%2F%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20-%C2%A3160%3CBR%20%2F%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20-%C2%A3151%3CBR%20%2F%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3CBR%20%2F%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%C2%A30%3CBR%20%2F%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%C2%A345%3CBR%20%2F%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%20data%2C%20I%20am%20after%20a%20formula%20that%20will%20always%20display%20the%20latest%20value%20based%20on%20two%20conditions%20and%20ignore%20blank%20values.%20E.g%3C%2FP%3E%3CP%3EIf%20week%20%3D%204%20%26amp%3B%20Period%20%3D%201%2C%20latest%20value%20to%20return%20is%20%3D%20-%C2%A3151%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20get%20an%20index%20match%20and%20lookup%20formula%20working%20for%20this%20need%2C%20however%20they%20both%20include%20the%20blank%20cells.%20I%20want%20the%20formula%20to%20ignore%20blank%20or%20zero%20values.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2319267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319312%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20latest%20value%20from%20table%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864626%22%20target%3D%22_blank%22%3E%40matt0020190%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20enter%20the%20week%20(4)%20in%20E2%20and%20the%20Period%20(1)%20in%20F2.%3C%2FP%3E%0A%3CP%3EFormula%20in%20G2%2C%20confirmed%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(C2%3AC13%2CMAX((A2%3AA13%3DE2)*(B2%3AB13%3DF2)*(C2%3AC13%26lt%3B%26gt%3B0)*(ROW(A2%3AA13)-ROW(A1))))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0368.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277637i312E3EEAA6863F77%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0368.png%22%20alt%3D%22S0368.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319382%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20latest%20value%20from%20table%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319382%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864626%22%20target%3D%22_blank%22%3E%40matt0020190%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20I%20no%20longer%20use%20traditional%20spreadsheets%2C%20the%20Excel%20365%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DXLOOKUP(1%2C%20(Week%3D4)*(Period%3D1)*ISNUMBER(Value)%2C%20Value%2C%2C%2C-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewould%20be%20my%20preference%2C%20but%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C%201%2F(Week%3D4)%2F(Period%3D1)%2FISNUMBER(Value)%2CValue)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eshould%20still%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all

This one is driving me mad.

Can anyone suggest a formula that will always get the latest value (and ignore blank values) from a table?

 

Week -----Period ------Value
1                   1               -£377
2                   1                £219
3                   1                £759
4                   1               -£1,106
1                   1                £302
2                   1                £409
3                   1                -£160
4                   1                -£151
1                   1
2                   1                 £0
3                   1                 £45
4                   1

 

In the above data, I am after a formula that will always display the latest value based on two conditions and ignore blank values. E.g

If week = 4 & Period = 1, latest value to return is = -£151

 

I have managed to get an index match and lookup formula working for this need, however they both include the blank cells. I want the formula to ignore blank or zero values. 

 

Any ideas please?

 

Many thanks

4 Replies

@matt0020190 

Let's say you enter the week (4) in E2 and the Period (1) in F2.

Formula in G2, confirmed with Ctrl+Shift+Enter to turn it into an array formula:

 

=INDEX(C2:C13,MAX((A2:A13=E2)*(B2:B13=F2)*(C2:C13<>0)*(ROW(A2:A13)-ROW(A1))))

 

S0368.png

best response confirmed by matt0020190 (Occasional Contributor)
Solution

@matt0020190 

Since I no longer use traditional spreadsheets, the Excel 365

=XLOOKUP(1, (Week=4)*(Period=1)*ISNUMBER(Value), Value,,,-1)

would be my preference, but

=LOOKUP(2, 1/(Week=4)/(Period=1)/ISNUMBER(Value),Value)

should still work.

Super thanks, this was what I was already using minus the isnumber check! thanks again
brilliant works well, just will prefer to use lookup as already got that in place. thanks for your solution