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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
What is you current formula?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)))))
 
Upvote 0
Solution
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?
 
Upvote 0
Did you wrap the formula with SUM, like I showed?
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top