Need to find the intersection within a table but output a cell within the same column

[laona]

Size of Tubing 16 21 27 35 41 53 63 78 91 103 116 129 155
AWG 14 12 22 36 62 85 140 200 200 200 200 200 200 200
AWG 12 9 16 26 45 62 102 145 200 200 200 200 200 200
AWG 10 5 10 16 28 38 63 90 139 187 200 200 200 200
AWG 8 3 5 9 16 22 36 52 80 100 138 173 200 200

Basically need to find the "Size of Tubing" based on "AWG #" (input 1) and another number within the body of the table. I need to find the intersection in the table but then output the intersection of the same column and the first row.

Example. If I input AWG 12 and input 16 how do i get the output 21?

Please help.

[sktneer]

Hi laona,
Welcome to the Forum.

Please find the attached sheet to see if this is what you are trying to achieve.

[laona]

Sktneer,

Formula worked. I was on the right track but was messing up the orders. Had to rearrange my table and change match type to -1 for greater than but other than that it worked like a charm.

Thank you!

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Size	16	21	27	35	41	53	63	78	91	103	116	129	155
2	AWG 14	12	22	36	62	85	140	200	200	200	200	200	200	200
3	AWG 12	9	16	26	45	62	102	145	200	200	200	200	200	200
4	AWG 10	5	10	16	28	38	63	90	139	187	200	200	200	200
5	AWG 8	3	5	9	16	22	36	52	80	100	138	173	200	200
6
7
8	AWG 12	16	21

This formula entered in C8:

=INDEX(B1:N1,MATCH(B8,INDEX(B2:N5,MATCH(A8,A2:A5,0),0),0))

[laona]

Tony,

Thanks for your help. Sktneer had the exact same idea. It works. Appreciate the help!

[Tony Valko]

Tony,

Thanks for your help. Sktneer had the exact same idea. It works. Appreciate the help!

You're welcome!

I think it's best to post any solutions in the reply so that EVERYONE can see it. People that are unable to download files won't know what the solution is.

[sktneer]

@Tony Valko

So sorry. As the OP didn't upload the sheet and I created one for him to show that how this can be achieved and also I was not aware of the sheet layout of OP's original workbook, that's why I thought it was useless to write the formula in the reply. But now I think there was no harm in sharing the formula along with the attached sheet.
I will definitely take care of this in future.

[Tony Valko]

@Tony Valko

So sorry. As the OP didn't upload the sheet and I created one for him to show that how this can be achieved and also I was not aware of the sheet layout of OP's original workbook, that's why I thought it was useless to write the formula in the reply. But now I think there was no harm in sharing the formula along with the attached sheet.
I will definitely take care of this in future.

Related...

https://www.excelforum.com/showthread.php?p=3325291

[sktneer]

@ laona
You're welcome. Glad I could help.