# Nearest Value to Average

I have a sample like this where I want to find nearest value to average

city and weight are two separate columns

``````city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23``````

I made a pivot and calculated the average of weight for A- which is 46.75

I need to find nearest number for A which will be 45 in this case

I think i need to use index and match, but how would i do it if have 17,000 rows with duplicate city names and different weight values?

Any help I would appreciate

so answer i am looking for is

``````Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23``````

INDEX(rawdata,MATCH(MIN(ABS(weight-\$B2)),ABS(weight-\$B2),0),2)

But it look at at whole array of weight from A-C. I oonly want it to look at values for A when it is comparing average of A,

And then weight of B when comparing average of B,

AND SO ON….

Please let me know what is wrong with my formula?