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
```

Most similar answers are not using this set, please help me set up this formula i have tried:

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?

thanks in advance

By: Birinder

here is the answer: =INDEX(rawdata,MATCH(SMALL(IF(combi=$A2,ABS(weight-$B2)),1),IF(combi=$A2,ABS(weight-$B2)),0),2)