Hi,
I know how to perform a CSE of the usual Index Match to nearest value which just looks at 1 column.
=INDEX(A7:A16,MATCH(TRUE,ABS(B7:B16-B2)=MIN(ABS(B7:B16-B2)),0))
How can I take this further to add the column index match?
For example, the data below isn't taking the name into account and purely just looking at the nearest value of 150 in column B.
I need it to look for the name aswell, is this possible?
John 150 returns Bandwith 9.
Karen 300 returns Bandwith 3.
<tbody>
</tbody>
I know how to perform a CSE of the usual Index Match to nearest value which just looks at 1 column.
=INDEX(A7:A16,MATCH(TRUE,ABS(B7:B16-B2)=MIN(ABS(B7:B16-B2)),0))
How can I take this further to add the column index match?
For example, the data below isn't taking the name into account and purely just looking at the nearest value of 150 in column B.
I need it to look for the name aswell, is this possible?
John 150 returns Bandwith 9.
Karen 300 returns Bandwith 3.
Name: | John | ||||
Value: | 150 | ||||
Returns: | Bandwith 9 | ||||
John | Dave | Jim | Karen | Jane | |
Bandwith 1 | 482 | 474 | 450 | 389 | 358 |
Bandwith 2 | 433 | 376 | 447 | 389 | 354 |
Bandwith 3 | 369 | 329 | 408 | 313 | 346 |
Bandwith 4 | 336 | 222 | 244 | 249 | 314 |
Bandwith 5 | 329 | 165 | 239 | 182 | 216 |
Bandwith 6 | 324 | 162 | 217 | 133 | 180 |
Bandwith 7 | 321 | 154 | 176 | 131 | 119 |
Bandwith 8 | 245 | 73 | 47 | 92 | 91 |
Bandwith 9 | 176 | 65 | 8 | 76 | 38 |
Bandwith 10 | 56 | 14 | 5 | 67 | 32 |
<tbody>
</tbody>
Last edited: