How to Index and match to return the last value from variable column

[abshmo5]

Hi again every one,

Im here again for some strange problem about my worksheet.

I have a set of table that contains data which some (column cell) are blank.
First Column was populated by date.
2nd Column was populated by shift.
the remaining column was populated by datas that are not consistent each column, some are blanks.

I want to Index and match to return the data of the last cell before the blank cell matching the 2 criteria (Date and Shift).

Please see attached file. ----> Index and match formula.xls

Thanks

Aie

[Ace_XL]

In Q9 use this array formula

=LOOKUP(2,1/(INDEX($C$2:$L$31,MATCH(O9&P9,$A$2:$A$31&$B$2:$B$31,0),)<>""),INDEX($C$2:$L$31,MATCH(O9&P9,$A$2:$A$31&$B$2:$B$31,0),))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy down

[abshmo5]

In Q9 use this array formula

=LOOKUP(2,1/(INDEX($C$2:$L$31,MATCH(O9&P9,$A$2:$A$31&$B$2:$B$31,0),)<>""),INDEX($C$2:$L$31,MATCH(O9&P9,$A$2:$A$31&$B$2:$B$31,0),))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy down

Very nice Ace_XL, i almost lost my mind thinking how and what formula to use.

Thanks

[:) Sixthsense :)]

Or

Non Array

=LOOKUP(99^99,OFFSET($C$1,LOOKUP(2,1/($A$2:$A$31&$B$2:$B$31=($O9&$P9)),ROW($B$2:$B$31))-ROW($B$2)+1,,,10))

[kvsrinivasamurthy]

IN Q9, and drag down.

Please Login or Register  to view this content.