Index and Match Problem

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I hope you can help. am having some problems getting my head around Index and Match.

I have a requirement to pull data from a specific cell in a sheet based on two Criteria.

The Criteria are Week number, and Project number, and I want to return a specific weeks info.

in my spreadsheet I have the following:
Column A1:A20 lists the Project numbers. Cols B1 to M1 are the week numbers. Cells B2:M20 are the updates.

The master sheet works on a week number and project number basis - I want to say: If Project ="22" and Week Number="39", then display that cells info.

I hope this makes sense?

Thanks
Scott
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
lol - 3 edits later because I cant do it right the first time:
What you are lookng forI think is a straight up index where your row of project crosses with your week number.

Index(A1:M20,match(22,A1:A20,0),match(39,A1:M1,0))

You could replace the 22 and 39 with a cell reference that you could change for a different project and week.

If you had data in 2 columns as mentioned above (Column A = Project, Column B = Week, Column C = other data) then it would be the following to return that "other data" in column C associated with 22 and 39

Index(A1:C20,match(1,(A1:A20=22)*(B1:B20=39),0),3)
 
Last edited:
Upvote 0
E1: 22 (a project number)
F1: 39 (a week number)

G1, just enter:

=INDEX($B$1:$M$20,MATCH(E1,$A$2:$A$20,0),MATCH(F1,$B$1:$M$1,0))

Or equivalently:

=VLOOKUP(E1,$A$2:$M$20,MATCH(F1,$A$1:$M$1,0),0)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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