Offset & Match Query

[Ana Dsouza]

Hey, thanks for your online learning platform.Awesome!!!!!!

Please find the attached Excel workbook containing data related to offset and match query.

-Cell E6 shows the output "C" which is incorrect according to the base data mentioned in the G and H columns. As in base data the code C02 is having only 2 top player i.e. A,B but in output its showing "C" also. (Column H - top player )

Looking forward to your help and also suggest which formulas I can use for this type of data calculation in the future.

Regards,




Attachments area

 

[AliGW]

In C4 copied across and down:

=IF(COUNTIF($G$4:$G$14,$B4)>=C$3,INDEX($H$4:$H$14,MATCH($B4,$G$4:$G$11,0)+C$3-1,1),"")

 

[Peter Bartholomew]

My favourite would be
= TRANSPOSE( FILTER( BaseData[Sub Div], BaseData[_Code]=@Code ) )
but, setting Office 365 aside for the moment,
= IF(
INDEX( BaseData[_Code], {0,1,2} + MATCH( @Code, BaseData[_Code], 0 ) ) = @Code,
INDEX( BaseData[Sub Div], {0,1,2} + MATCH( @Code, BaseData[_Code], 0 ) ),
"" )

p.s. the underscore before _CODE is to allow the formula to print correctly as a post.

 

[Ana Dsouza]

Thanks a lot