Index and Match Problem

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
23
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

holystoner

New Member
Joined
Oct 1, 2014
Messages
12
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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