#### JoshTheTofu

##### New Member
Hi guys

I have been using the INDEX function to find certain cells in an array and I was told today I need to add the cell to the right of each cell that the index function finds.

I can do this by creating another index function and simply adding the two together =INDEX()+INDEX() but I already know how to do that and I won't learn anything from it. Does anybody know of a different way to do this?

Kind regards

Josh

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Fluff

##### MrExcel MVP, Moderator
What is you current formula?

#### JoshTheTofu

##### New Member
Excel Formula:
``=INDEX('Other sheet'!\$D\$5:'Other sheet'!\$XW\$19,XMATCH(TRUE,"Column value"='Other sheet'!\$C\$6:INDEX('Other sheet'!\$C:\$C,1000)),XMATCH(1,(JR2='Other sheet'!\$D\$2:INDEX('Other sheet'!\$2:\$2,1000))*("Heading"='Other sheet'!\$D\$4:INDEX('Other sheet'!\$4:\$4,1000))))``

This is the formula I use to find the cell and if I was to do this myself I would just copy the formula change the "Heading" and add them together. I'm sure there is a better way to do this.

#### Fluff

##### MrExcel MVP, Moderator
Firstly lets simplify that like
Excel Formula:
``=INDEX('Other sheet'!\$D\$5:\$XW\$19,XMATCH("Column value",'Other sheet'!\$C\$5:\$C\$19,0),XMATCH(1,(JR2='Other sheet'!\$D\$2:\$XW\$2)*("Heading"='Other sheet'!\$D\$4:\$XW\$4)))``
Does it still work?

#### JoshTheTofu

##### New Member

It works absolutely amazingly. Thank you.

Just this alone helps me so much.

These were some of the first formulas I made in Excel, so I expect them to be a little bit cumbersome, thank you so much for this.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=SUM(INDEX('Other sheet'!\$D\$5:\$XW\$19,XMATCH("Column value",'Other sheet'!\$C\$5:\$C\$19,0),SEQUENCE(,2,XMATCH(1,(JR2='Other sheet'!\$D\$2:\$XW\$2)*("Heading"='Other sheet'!\$D\$4:\$XW\$4)))))``

#### JoshTheTofu

##### New Member

Hi Fluff

I have just got back to the sheet and it is very close. This formula puts the numbers adjacent to each other (it spills out on the right-hand side), is there a way to then add the numbers together and display this total in the cell?

#### Fluff

##### MrExcel MVP, Moderator
Did you wrap the formula with SUM, like I showed?

#### JoshTheTofu

##### New Member
Hi Fluff

I'm really embarrassed I didn't see that, my apologies.

I can confirm that it all works and I have already impemented the simplification into the sheet and will update it with this too.

Many thanks

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
132
Replies
1
Views
541
Replies
2
Views
86
Replies
3
Views
214
Replies
6
Views
152

1,136,968
Messages
5,678,870
Members
419,787
Latest member
juanam

### 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