Hello!
I need help on using the match function across multiple columns. Here is my example:
<tbody>
</tbody>
<tbody>
</tbody>
I want a cell to return the width: 32
I want to be able to specify the Column (Angle) and a Capacity value and return the Width that has the closest Capacity value greater than specified Capacity.
I know how to use the INDEX and MATCH functions together for a single column, but not on multiple columns.
A less elegant way is to use nested IF's like so:
=IF(ANGLE="0 deg",INDEX(A3:A5,MATCH(CAPACITY,B3:B5,-1)),IF(ANGLE="5 deg",INDEX(A3:A5,MATCH(CAPACITY,C3:C5,-1)),IF(ANGLE="10 deg",INDEX(A3:A5,MATCH(CAPACITY,D3:D5,-1)),"")))
I have a table with multiple columns and don't want to write an "IF" for each column. I want a formula to know use the MATCH function on the specified column.
Thank you!
I need help on using the match function across multiple columns. Here is my example:
Capacities | Angle | Angle | Angle |
Width | 0 deg | 5 deg | 10 deg |
32 | 555 | 444 | 333 |
18 | 433 | 420 | 290 |
12 | 306 | 285 | 222 |
<tbody>
</tbody>
Capacity | 315 |
Angle | 10 deg |
<tbody>
</tbody>
I want a cell to return the width: 32
I want to be able to specify the Column (Angle) and a Capacity value and return the Width that has the closest Capacity value greater than specified Capacity.
I know how to use the INDEX and MATCH functions together for a single column, but not on multiple columns.
A less elegant way is to use nested IF's like so:
=IF(ANGLE="0 deg",INDEX(A3:A5,MATCH(CAPACITY,B3:B5,-1)),IF(ANGLE="5 deg",INDEX(A3:A5,MATCH(CAPACITY,C3:C5,-1)),IF(ANGLE="10 deg",INDEX(A3:A5,MATCH(CAPACITY,D3:D5,-1)),"")))
I have a table with multiple columns and don't want to write an "IF" for each column. I want a formula to know use the MATCH function on the specified column.
Thank you!