Forum Discussion
How do you reference a cell which tracks being sorted?
Thanks for the advice. I have read and re-read the VLOOKUP description and am confused.
I have the unique text string to use as a key in column C such as "Diagnostics Services"
I have an integer in column E which is the result to be retrieved.
The table has 74 rows including the one row header.
What should the VLOOKUP statement be to retrieve the integer?
In your provided example below, it seems that there may be a better way to average multiple integers than =AVERAGE( VLOOKUP(params1), VLOOKUP(params2), VLOOKUP(params3))
How might that work?
Thanks in advance, this would be a big help!
Do I understand correctly you sort all columns?
When it could be
=AVERAGEIFS($E$2:$E$33,$C$2:$C$33,$G$7)I want to average particular cells in a column which may be moved around due to sorting.
Content based keys with a table offset seems one way to go.
AFAIK using $ is to fix a cell used in a calculation, despite copy and pasting the formula to other cells. I don't see how this would help when the cells of interest move around.
Do you sort only this one column or you re-arrange few columns based on sorting one? If few columns are re-arranged you may use AVERAGEIFS() instead of AVERAGE() to select cells to average based on criteria, aka key in description column.
The whole table is sorted by the values in a column, so the cells in each row stay associated.
The table may be sorted by sorting of any column.