# If INDEX/MATCH?

#### slam

##### Well-known Member
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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

Last edited:
=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

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
BCDEFGHIJK
2F1 201x22a
333b
444c
555e
666f
777g
Setup

Excel 2010
ABCD
1a1
2F1 201xb2
3c3
4d4
5F1 201xa5
6f6
7g7
Circuits

Excel 2010
FGH
252#N/A
Sheet3
Cell Formulas
RangeFormula
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

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!

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

Replies
1
Views
2K
Replies
1
Views
264
Replies
18
Views
664
Replies
6
Views
381
Replies
4
Views
288

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back