# Index and Match Problem

#### scottmcclean

##### New Member
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### holystoner

##### New Member
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:

#### scottmcclean

##### New Member
Thanks holystoner, it works a treat!!

#### Aladin Akyurek

##### MrExcel MVP
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)

Threads
1,102,666
Messages
5,488,184
Members
407,631
Latest member
Mehezabin

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...