Index, Match, Dynamic column doesn't work

Hi there,

Please Login or Register  to view this content.

This refers to a Range which begins on Row 5

This identifies the first instance of "olsen" which occurs in a range beginning on Row 1

If the first instance of "olsen" occurs on Row (e.g.) 6, the MATCH function will return a value of 6, but the INDEX function will return the sixth row of the Range which begins on Row 5, i.e. Row 10.


Hope this helps.

Regards,

Greg M

Hi Greg,
'olsen' is at cell FG26. FG26 = .682.

Still confused.
Chuck

no sample file

Please Login or Register  to view this content.

Administrative note
Your profile indicates XL2003. Is this still the case? If not please update your profile

Read our magnificent banner to attach a sheet

Hi protonLeah,
You did it. Many thanks. I wouldn't have guessed about counta.
Why did COUNTA work and FG:FG did not?

Chuck - from Monrovia, Ca.

Originally Posted by Greg M

Hi there,

Please Login or Register  to view this content.

This refers to a Range which begins on Row 5
This identifies the first instance of "olsen" which occurs in a range beginning on Row 1
If the first instance of "olsen" occurs on Row (e.g.) 6, the MATCH function will return a value of 6, but the INDEX function will return the sixth row of the Range which begins on Row 5, i.e. Row 10.

---

Using FG:FG for the match, if there is a match above row 5 (the first row of the INDEX range), then the INDEX function will return the value from column FK which is obviously 0.
To create a dynamic range starting at row 5, and ending at the row corresponding to the number of values in column FG, I used:

'New Player Avgs (2)'!$FG5:INDEX('New Player Avgs (2)'!FG:FG,COUNTA('New Player Avgs (2)'!FG:FG))

**Note: if there are values in FG above row 5 the bottom of the range will be off by the number of items above row 5 and you will have to modify the formula to:
...COUNTA('New Player Avgs (2)'!FG:FG) + x...
x - the number of used rows above FG5.

if you mean to replace the MATCH portion, then no. In this case it only tells you the number of used rows in a column, not the row of a particular item. One alternative is to create a dynamic named range, say, Player_Avg, with:

Please Login or Register  to view this content.

Then the cell formula will be simpler:

Please Login or Register  to view this content.