If INDEX/MATCH?

[slam]

I'm trying to create a formula which I will copy horizontally across 40 cells, which says (in my hybrid formula/english version )


This would be the formula in F2


If Setup!$B$2="F1 201x", display the value from Circuits!$D:$D where Setup!$B$2 and Setup!$K2 matches Circuits!$A:$A and Circuits!$B$B, else Setup!$J2


This would be the formula in G2


If Setup!$B$2="F1 201x", display the value from Circuits!$D:$D where Setup!$B$2 and Setup!$K3 matches Circuits!$A:$A and Circuits!$B$B, else Setup!$J3

And so on through AS2...


Do I need an index/match for the value if true? Not sure how to proceed, and obviously copying a formula horizontally that references a vertical range of cells..... Any help would be greatly appreciated.

 

[Simon4s]

I'm trying to create a formula which I will copy horizontally across 40 cells, which says (in my hybrid formula/english version )


This would be the formula in F2


If Setup!$B$2="F1 201x", display the value from Circuits!$D:$D where Setup!$B$2 and Setup!$K2 matches Circuits!$A:$A and Circuits!$B$B, else Setup!$J2


This would be the formula in G2


If Setup!$B$2="F1 201x", display the value from Circuits!$D:$D where Setup!$B$2 and Setup!$K3 matches Circuits!$A:$A and Circuits!$B$B, else Setup!$J3

And so on through AS2...


Do I need an index/match for the value if true? Not sure how to proceed, and obviously copying a formula horizontally that references a vertical range of cells..... Any help would be greatly appreciated.

=if(setup!$b$2="F1 201x", sumifs(circuits!$D2,setup!$b2,circuits!$A2,setup!$k2,circuits!$b2),setup!$j2). copy and paste it downwards.

This is what you wanted?: if setup b2 equals "f1 201x" then sum circuits D2 where setup b2 is equal to circuits A2 and setup k2 is equal to circuits b2. or else setup j2

 

[slam]

=if(setup!$b$2="F1 201x", sumifs(circuits!$D2,setup!$b2,circuits!$A2,setup!$k2,circuits!$b2),setup!$j2). copy and paste it downwards.

This is what you wanted?: if setup b2 equals "f1 201x" then sum circuits D2 where setup b2 is equal to circuits A2 and setup k2 is equal to circuits b2. or else setup j2

Ummm... I don't think so

Firstly, I'm not copying downward, I'm copying across, so I think I need to use INDIRECT to flip that around.

Secondly, no, not trying to sum anything. I'm trying to display the one value from column D on circuits when the one instance of the combination of column A and column B on circuits match the one combination of Setup B2 and Setup K2

For instance, imagine the following values:

Setup B2 = F1 201x
Setup K2 = Circuit1

Circuits A434 = F1 201x
Circuits B434 = Circuit1
Circuits D434 = ABC

the formula result would be ABC

Hopefully that's clear?

Thank you

 

[LloydFinancials]

Hi Slam,

Here is another option: You have to decide what you want to do if it is N/A#. Maybe this using Control+Shift+Enter.
=IFERROR(IF(Setup!$B$2="F1 201x",INDEX(Circuits!$D1:$D99999,MATCH(Setup!$B2&INDIRECT("Setup!$K"&COLUMN(B1)),Circuits!$A1:$A9999&Circuits!$B1:$B9999,0)),INDIRECT("Setup!$J"&COLUMN(B1))),"")

Excel 2010
	B	C	D	E	F	G	H	I	J	K
2	F1 201x								22	a
3									33	b
4									44	c
5									55	e
6									66	f
7									77	g
Setup

Excel 2010
	A	B	C	D
1		a		1
2	F1 201x	b		2
3		c		3
4		d		4
5	F1 201x	a		5
6		f		6
7		g		7
Circuits

Excel 2010
	F	G	H
2	5	2	#N/A
Sheet3

Cell Formulas
Range	Formula
F2	{=IF(Setup!$B$2="F1 201x",INDEX(Circuits!$D1:$D99999,MATCH(Setup!$B2&INDIRECT("Setup!$K"&COLUMN(B1)),Circuits!$A1:$A9999&Circuits!$B1:$B9999,0)),INDIRECT("Setup!$J"&COLUMN(B1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Luke

 

[slam]

Thanks Luke,

Both those formulas work. I think I see what you say about handling errors. I think I'd only encounter an error if there were blanks in the range Setup!K2:K41 (let me know if you think I might have other errors?). If there are blanks in that range, I see your first formula forces a blank. I changed that part to revert to the Setup!J2:J41 reference:

=IFERROR(IF(Setup!$B$2="F1 201x",INDEX(Circuits!$D1:$D99999,MATCH(Setup!$B2&INDIRECT("Setup!$K"&COLUMN(B1)),Circuits!$A1:$A9999&Circuits!$B1:$B9999,0)),INDIRECT("Setup!$J"&COLUMN(B1))),INDIRECT("Setup!$J"&COLUMN(B1)))

Looks to be working. Thanks so much!

 

[LloydFinancials]

Great. Glad I could help. I don't think you will see any other errors unless your formula goes past 9999 or whatever you have for the last column. I put 9999s in there because the formula slows a bit when searching the whole column. Let me know if you need anything else. Luke