# Thread: Index and Match Problem Thanks: 0 Likes: 0

1. ## Index and Match Problem

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

2. ## Re: Index and Match Problem

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)

3. ## Re: Index and Match Problem

Thanks holystoner, it works a treat!!

4. ## Re: Index and Match Problem

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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•