Hi,



I will like to write a formula to return a certain value in excel.
Basically, say if C4 cell contains x,y, or z then I want D4 to find the last cell (in colum C) with x, y or z and return a value equal the corresponding D value +1

Any help at all will be appreciated. I can give more explanation if this isn't clear.

Thanks

Supposing your range is C4:d14 try =INDEX(D4:d14,(MAX((C4:C14=$C$4)*ROW(4:14))-ROW(4:14)+1))

This is an array formula that should be committed with Ctrl+Shift+Enter

Attached Files

CheckMax.xlsx (9.1 KB, 8 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Try this (change the ranges to suit your sheet). Of course, the brackets indicate that this is an array formula so when you enter it you need to press CNTL-SHIFT-ENTER.

{=LOOKUP(2,1/(C6:C15=C4),D6:d15)+1}

@gebobs

Not a big deal but I don't think array entering is required.

Right you are!

Thanks for getting back to me, it doesn't exactly give me what I want.

I more or less need a formula to continue a numbering sequence based on the value in the adjacent cell.
If column C has values x, y or z and D contain numbering sequence based on what is in C
so say C10= x then D10 checks for the last C cell with x value (before C10) and returns the number in the D cell +1

Is it something like the attached?

Attached Files

MKKYB.xlsx (10.3 KB, 10 views)

Thanks, yes something like that. Thanks for your help