find exact Matching column

[sifar786]

Hi,

i am stuck with a problem in matching a column using Match function.

Problem:
---------
i have 4 columns with numbers as headers viz

StartDate: =NOW()

No of Projects
Tasks 2 4 6 >=7
----------------------------
task 1 1.1 2.38 3.24 1.33
task 2 1.5 2 1.2 2.12
task 3 3.2 5 2.2 2.56
task 4 2.9 3.96 1.3 4

so the above 2,4,6,8 generally mean no of projects and the time in hours for each task completion as shown below.

now i am using INDEX(MATCH, MATCH) to get the above data into a below table which shows a date and time for each task from the StartDate, excluding Holidays and Weekends (i am using WORKDAY function).

Tasks 1 7 3 8 11
Rpt 1 Rpt 2 Rpt 3 Rpt 4 Rpt 5
--------------------------------------
task 1 Dt Dt Dt Dt Dt
task 2 Dt Dt Dt Dt Dt
task 3 Dt Dt Dt Dt Dt
task 4 Dt Dt Dt Dt Dt

so this 2nd table shows that for each consecutive task:

Rpt1 has 1 project and it needs to pull hours from "2" column from the 1st table.
Similarly, Rpt2 has 7 Projects and needs to pull hours from ">=7" column.

Here's where the trouble occurs. MATCH for column in INDEX function is unable to do an exact match (match_Type=0) & an approximate match (match_Type=1) yields incorrect results as its not able to find the above Project nos (1,7,3, 11) in 2nd table.

So ideally looking at 2nd table, for each Task row,
if Project value is 1 or 2, then MATCH should pull data from 2 column.
if Project value is 3 or 4, then MATCH should pull data from 4 column.
if Project value is 5 or 6, then MATCH should pull data from 6 column.
if Project value is >=7, then MATCH should pull data from 8 column.

How can this be achieved using MATCH function for Column using INDEX function?

INDEX(MATCH(ROW), MATCH(COLUMN???))

Any ideas how this can be achieved?

 

[texasalynn]

one problem is that your data needs to be sorted in ascending order. Your "3" is out of order