column and row intersections

drubin25

New Member
Joined
Mar 19, 2016
Messages
22
I am struggling on this one...

I have names in A4:A15 and dates in B3:AF3 on a tab named "Total Completions_Monthly". Cells B4:AF15 are blank and I want to populate them with values where two criteria are met from the raw data in the Sheet1 tab.

If the name in A4:A15 "Total Completions_Monthly" is found in column A (Sheet1) and if the date in B3:AF3 "Total Completions_Monthly" is found in row 2...I want to find the number where the name and date intersect.

For example:
I want to search for Bob on 2-Sep-20 in Sheet1 A2:AE15 and place the value where the criteria is met on the "Total Completions_Monthly" tab where Bob and 2-Sep-20 meet.

Hopefully I am making sense. Any help is appreciated.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,976
Perhaps =INDEX(B3:AE15, MATCH("Bob",A3:A15,0), MATCH("2-Sep-20",B2:AE2,0)
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
22
Thank you. I used the following based on what you provided and it seems to have worked.

=IFERROR(INDEX(Sheet1!$B$3:$AE$15,MATCH('Total Completions_Monthly'!$A4,Sheet1!$A$3:$A$15,0),MATCH('Total Completions_Monthly'!B$3,Sheet1!$B$2:$AE$2)),0)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,819
Members
416,342
Latest member
BlueDevil12

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