Excel 2007 : Offset - Positional Reference

I'm trying to return the value of a cell when a match is found. I'm using the cells position to determine the return value. The position of the cell value is always the same RELATIVE to another criteria (ie TYPE II CO, REPASS, etc), which is why i'm using match, address, and offset. All of the code works so far except the last section (offset). The offset works for one position, but I want to position to vary based on the match.

Here is my code so far:

IF(AND(VLOOKUP(B20,A1:A16,1,TRUE),INDIRECT(ADDRESS(MATCH(B20,A1:A16,0)+1,1))="blank", INDIRECT(REPLACE(ADDRESS(MATCH(B20,A1:A16,0)+1,2),1,3,"B"))="TYPE II CO"),OFFSET(B4,-2,7,1,1),"nope")

My problem/question:
I want the reference cell in offset to be the position of a matched cell, however I get the following error when I try and input the position formula into offset "error..... too many arguments". I then tried to use the formula separately and feed offset the position of the cell, but offset is interpreting the actual position of the cell, not the referenced position (value) inside the cell.

How do I get offset to accept the position value inside a cell and not use the actual position of the cell?

Thanks, Let me know if the situation is not clear.Offset_Code_excelforums.xlsx

I am not sure I understand the logic your after, but does this come close?

=IFERROR(INDEX($I:$K,MATCH(B20,A:A,0)-1,MATCH(C19,$I$1:$K$1,0)),"nope")

This should get you the TIME 1 based on TYPE II CO...

Please Login or Register  to view this content.

You need to just adjust the name (TYPE II CO) and the position for offset (i.e. the -2)

I try to avoid OFFSET function when I can since it is a Volatile function. Sometimes it leads to more complex formulas, but most of the time it is worth it to avoid constant re-calculations...

So what I did was first find the Match for B20 in A1:A16, then added the MATCH of "TYPE II CO" in the subset of B1:B16 starting from row where B20 matched A1:A16 to end (B16)

If you use the formula evaluator found in the Formula tab, you will be able to follow the steps.

For the spreadsheet attachedindex_match_sum_example.xlsxindex_match_sum_example.xlsx how could I modify the code would so it would give me a sum of the runq1 time (if that was the value to be searched for)? For example, I tell the code to sum all the runq1 and repass times, and it would (but it would keep them separate, repass:79min runq1:1045).

Currently the code only finds the first value of the label searched for.

The code is in the boxes below ALLOC, BATCH,... etc.

I think the easiest way is to add a helper column, that will occupy each cell with relevant ItemID from column A.

So in I2 enter formula:

=LOOKUP(9.9999E+307,A$2:A2)

copied down

now in G16:

=SUMIFS($F$2:$F$14,$I$2:$I$14,$F16,$B$2:$B$14,G$15)

copied across and down, if you have more to check...