Occurrence formula not working

[Tdeg88]

Hi,

So I would appreciate some help as my formula doesn't seem to be working, having tried variation of using IF before the INDEX. I'm using the formula below in a different sheet (but the same worksheet) pulling information from sheet 1 (the information below is from sheet 1). I'm using the if statement to filter by Car type ideally (I would like to use two types in the formula there is only!), the second argument in the formula, I need the information in the if statement to either be greater than 0 the "Number of remaining column" or not have the text "No Turns Left" in the "Next turn date column".

=INDEX(Sheet1!G:G,MATCH(MODE.SNGL(IF(AND(Sheet1!A:A="DM",Sheet1!E:E>0),Sheet1!G:G,"")),Sheet1!G:G,0))

					Today:	13/10/2022
Car Type	Last Turn Date	Post-Turn Diameter	Spare Diameter Remaining (mm)	Number of Remaining Turn Cycles	Next Turn Date
DM	31/01/2020​	716.5​	11.5​	0​	NO TURNS LEFT
DM	31/01/2020​	716.49​	11.49​	0​	NO TURNS LEFT
DM	31/01/2020​	719.49​	14.49​	0​	NO TURNS LEFT
DM	31/01/2020​	718.39​	13.39​	0​	NO TURNS LEFT
T	31/01/2020​	731.97​	41.97​	5​	29/12/2020​
T	31/01/2020​	732.96​	42.96​	5​	29/12/2020​
T	31/01/2020​	731.91​	41.91​	5​	29/12/2020​
T	31/01/2020​	731.96​	41.96​	5​	29/12/2020​
UNDM	31/01/2020​	725.04​	20.04​	2​	29/12/2020​
UNDM	31/01/2020​	724.49​	19.49​	0​	NO TURNS LEFT
UNDM	31/01/2020​	724.47​	19.47​	0​	NO TURNS LEFT
UNDM	31/01/2020​	725.49​	20.49​	2​	29/12/2020​
ST	31/01/2020​	697.93​	7.93​	0​	NO TURNS LEFT
ST	31/01/2020​	697.99​	7.99​	0​	NO TURNS LEFT
ST	31/01/2020​	698.02​	8.02​	0​	NO TURNS LEFT
ST	31/01/2020​	698.09​	8.09​	0​	NO TURNS LEFT
DM	15/06/2020​	757.93​	52.93​	5​	14/05/2021​
DM	15/06/2020​	754.95​	49.95​	5​	14/05/2021​
DM	15/06/2020​	757.8​	52.8​	5​	14/05/2021​
DM	15/06/2020​	758.05​	53.05​	5​	14/05/2021​
T	15/06/2020​	721.94​	31.94​	4​	14/05/2021​
T	15/06/2020​	724.93​	34.93​	4​	14/05/2021​
T	15/06/2020​	722.02​	32.02​	4​	14/05/2021​
T	15/06/2020​	721.94​	31.94​	4​	14/05/2021​
UNDM	15/06/2020​	766.18​	61.18​	6​	14/05/2021​
UNDM	15/06/2020​	764.96​	59.96​	6​	14/05/2021​
UNDM	15/06/2020​	767.43​	62.43​	6​	14/05/2021​
UNDM	15/06/2020​	766.02​	61.02​	6​	14/05/2021​
DM	10/07/2019​	733​	28​	0​	NO TURNS LEFT
DM	10/07/2019​	733​	28​	0​	NO TURNS LEFT
DM	10/07/2019​	736​	31​	3​	07/06/2020​
DM	10/07/2019​	736​	31​	3​	07/06/2020​
T	10/07/2019​	736​	46​	6​	07/06/2020​
T	10/07/2019​	736​	46​	6​	07/06/2020​
T	10/07/2019​	736.98​	46.98​	6​	07/06/2020​
T	10/07/2019​	737​	47​	6​	07/06/2020​
UNDM	10/07/2019​	733​	28​	0​	NO TURNS LEFT
UNDM	10/07/2019​	734​	29​	0​	NO TURNS LEFT
UNDM	10/07/2019​	734​	29​	0​	NO TURNS LEFT
UNDM	10/07/2019​	734​	29​	0​	NO TURNS LEFT

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

 =INDEX(Sheet1!G:G,MATCH(MODE.SNGL(IF((Sheet1!A:A="DM")*(Sheet1!E:E>0),Sheet1!G:G,"")),Sheet1!G:G,0))

although there is nothing in col G

 

[Tdeg88]

Hi Fluff,

Thank you for the quick response. Rookie mistake I copied the formula and then deleted a column.

However even with the correct column reference I still get #N/A

=INDEX(Sheet1!F:F,MATCH(MODE.SNGL(IF(AND(Sheet1!A:A="DM",Sheet1!E:E>0),Sheet1!F:F,"")),Sheet1!F:F,0))

 

[Fluff]

Did you try the change I suggested?

 

[Tdeg88]

Apologies, it does work my mistake.

Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.