Index and match function spanning multiple columns and rows

L8NightKebab

New Member
Joined
Sep 11, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi im working on 2 sheets and im trying to use the index and match function to match a persons name on a range of columns on another sheet and get the data thats associated with that name, the data is always on the same row number off set by 1 column to the right, I currently use "=INDEX(Sunday!$F$5:$F$16, MATCH(F11, Sunday!$E$5:$E$16, 0))" and can get the data when the users information is in a single array however due to the structure of the spread sheet there are multiple rows and columns
 

Attachments

  • Screenshot 2024-09-12 013119.jpg
    Screenshot 2024-09-12 013119.jpg
    66.1 KB · Views: 11
  • Screenshot 2024-09-12 013317.jpg
    Screenshot 2024-09-12 013317.jpg
    29.1 KB · Views: 13

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try.
=FILTER(Sunday!$F$5:$F$16, Sunday!$E$5:$E$16=F11 )
Sorry for the late reply, I found a fix:
Excel Formula:
=IFERROR(INDEX(SUNDAY!$E$5:$E$67,MATCH($F5,SUNDAY!$D$5:$D$67,0)),IFERROR(INDEX(SUNDAY!$H$5:$H$61,MATCH($F5,SUNDAY!$G$5:$G$61,0)),0))
I'm sure there is a more efficient way to do this but I just created a if error statement and did 2 seperate index match functions
 
Last edited by a moderator:
Upvote 0
Solution

Forum statistics

Threads
1,224,567
Messages
6,179,562
Members
452,926
Latest member
rows and columns

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