Can somebody breakdown what these functions are doing?

[Declamatory]

I understand what this formula does in that it works like a VLOOKUP without the array. But I don't understand what each of the functions are doing and how it works.

I've used this formula in a number of my workbooks but don't like not understanding.

If somebody could explain it to me I would be grateful.

Thanks

=INDEX(Citi!$G$2:$G$30,SUMPRODUCT((Citi!$K$2:$K$30=J19)*ROW(Citi!$K$2:$K$30))-1)

 

[BarryL]

firstly I suggest going to Formulas on the ribbon then to Evaluate Formula and step through it.

So INDEX works by giving an array then telling it how many rows to go down/columns to go across. In this case we have the Area G2:G30. The SUMPRODUCT looks at each cell in column K and sees if it is equal to J19. if it is it is TRUE, if not FALSE. Important to note that TRUE/FALSE numerical values are 1/0 respectively. so then it multiplies the True/False by the row number.

Example. If K9 did equal J19 then it would be TRUE*9 (the row number) or alternatively 1*9. This would give 9

Then your formula has -1 so it would give 8. It would then give the value of G9.

 

[steve the fish]

Its not a formula I would use personally. It could give incorrect results should J19 appear in K2:K30 more than once. I suppose it depends if that's possible.

 

[Declamatory]

Example. If K9 did equal J19 then it would be TRUE*9 (the row number) or alternatively 1*9. This would give 9

Then your formula has -1 so it would give 8. It would then give the value of G9.

Thanks for the explanation. I think I've got it apart from the -1 bit if it gives 8 how does it bring through the value in G9?