Index and Match Problem

[scottmcclean]

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

 

[holystoner]

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)

 

[scottmcclean]

Thanks holystoner, it works a treat!!

 

[Aladin Akyurek]

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)