match multiple columns

[rachelby]

Hello!

I need help on using the match function across multiple columns. Here is my example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Capacities[/TD]
[TD]Angle[/TD]
[TD]Angle[/TD]
[TD]Angle[/TD]
[/TR]
[TR]
[TD]Width[/TD]
[TD]0 deg[/TD]
[TD]5 deg[/TD]
[TD]10 deg[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]555
[/TD]
[TD]444[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]433[/TD]
[TD]420[/TD]
[TD]290[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]306[/TD]
[TD]285[/TD]
[TD]222[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Capacity[/TD]
[TD]315[/TD]
[/TR]
[TR]
[TD]Angle[/TD]
[TD]10 deg[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[BarryL]

is it something like the below?

=INDEX(A:A,MATCH("10 deg",CHOOSE(MATCH(CAPACITY,$B$2:$D$2,0),B:B,C:C,D:D),-1))

where the choose function will dictate which column the match will perform its function on. If therer are more degrees to choose from expand the choose function out.

 

[Fluff]

Another option

Excel 2013/2016
	A	B	C	D	E	F
1	Capacities	Angle	Angle	Angle
2	Width	0	5	10
3	32	555	444	333		315
4	18	433	420	290		10
5	12	306	285	222		32
Sheet2

Cell Formulas
Range	Formula
F5	=INDEX(A3:A5,MATCH(F3,INDEX(B3:D5,0,MATCH(F4,B2:D2,0)),-1))

 

[rachelby]

Another option

Excel 2013/2016

	A	B	C	D	E	F
Capacities	Angle	Angle	Angle
Width

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"][/TD]
[TD="align: right"]315[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=INDEX(A3:A5,MATCH(F3,INDEX(B3:D5,0,MATCH(F4,B2:D2,0)),-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

THANK YOU!!! WORKED PERFECTLY!

 

[rachelby]

thank you!!! Worked perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback