Complex (for me) Index + Match Problem

[George__]

Good morning,

I'm trying to figure out a formula for this

1. Look horizontally for the unique header value (A B C D E F G)
2. Then vertically search column by column for the part # (it's always column 0)
3. Give value for stock (always column 2)

Sheet 1
Part # Stock
390-2 | function
219-2 |
210-3 |

Sheet 2

unique value | space | unique value
part # | stock | space | part # | stock


Any insight?

Thanks

 

[James006]

Hello,

Your three steps are :

1. =match("A",A1:Z1,0) to get ColNb
2. =match(12,A:A,0) to get RowNb
3. then you can use Index
=Index(yourRange, RowNb,ColNb)

Hope this will help

 

[George__]

Hello,

Your three steps are :

1. =match("A",A1:Z1,0) to get ColNb
2. =match(12,A:A,0) to get RowNb
3. then you can use Index
=Index(yourRange, RowNb,ColNb)

Hope this will help

[TABLE="width: 350"]
<tbody>[TR]
[TD="width: 70"][/TD]
[TD="width: 70, align: right"]1[/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70, align: right"]2[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Used in[/TD]
[TD][/TD]
[TD]Part[/TD]
[TD]Used in[/TD]
[/TR]
[TR]
[TD]G58B[/TD]
[TD]Y-3[/TD]
[TD][/TD]
[TD="class: xl63"]904-A[/TD]
[TD="align: right"]3212[/TD]
[/TR]
[TR]
[TD]58B[/TD]
[TD]C-3[/TD]
[TD][/TD]
[TD="class: xl63"]904-B[/TD]
[TD="align: right"]3258[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Used In[/TD]
[TD="colspan: 2"]Warehouse[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G58B[/TD]
[TD]Y-3 (formula)[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]58B[/TD]
[TD="align: right"]formula [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]904-A[/TD]
[TD="align: center"]formula [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]904-B[/TD]
[TD="align: center"]formula [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Warehouse is entered by hand but I am trying to create a SUMPRODUCT formula to grab it based on the part #

=INDIRECT(ADDRESS(SUMPRODUCT(($A$1:$F$6=A10)*ROW($A$1:$A$6))-2,(SUMPRODUCT(($A$3:$F$6=A10)*COLUMN($A$1:$F$1))+1)))

I need to change the ROW -2 and COLUMN +1 when I drag it down

Formula for part #
=INDEX($A$3:$N$6,MATCH(C9,$A$1:$Z$1,0),MATCH(A9,$A:$A,0))


How do I make excel figure this out?

 

[George__]

I think I figured out the Index one
=INDEX($B$2:$E$4,SMALL(IF(INDEX($A$2:$E$4,,MATCH(C9,$A$1:$E$1,0))<>"",IF($A$2:$A$4=A9,ROW($A$2:$E$4)-ROW($A$2)+1)),1),MATCH(C9,$B$1:$E$1,0))

 

[James006]

Hello again,

Think you should solve one problem after the other ...

Regarding the Formula for Part #, it should read as follows

=INDEX($A$1:$N$6,MATCH(C9,$A$1:$N$1,0),MATCH(A9,$A1:$A6,0))

HTH

 

[George__]

Hello again,

Think you should solve one problem after the other ...

Regarding the Formula for Part #, it should read as follows

=INDEX($A$1:$N$6,MATCH(C9,$A$1:$N$1,0),MATCH(A9,$A1:$A6,0))

HTH

Thank you

IS what I'm trying to do called Matrix Searching?

I can grab data perfectly from the first table but if I enter data to look in table 2 I get errors -_-"

 

[AhoyNC]

Maybe something like this.
Formula in C9 is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formulas down as needed.

Excel Workbook
	A	B	C	D	E
1		1			2
2	Part	Used in		Part	Used in
3	G58B	Y-3		904-A	3212
4	58B	C-3		904-B	3258
5
6
7
8	Part	Used In	Warehouse
9	G58B	Y-3	1
10	58B	C-3	1
11	904-A	3212	2
12	904-B	3258	2
Sheet