Using INDEX to find a cell then adding adjacent cell

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
What is you current formula?
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
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)))))
 
Solution

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
Did you wrap the formula with SUM, like I showed?
 

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top