Index Match on two columns

[Huddle]

I've attached an example of what I am trying to do. Currently the orange formula brings in the building number of the first building that matches the criteria of the days out. I want to find the building that matches the days out and the commander. I've tried two formulas one in yellow and one in pink. I'm not able to make either work. Can you see my error?

[NBVC]

The "yellow" formula:

=IF(N3>0,INDEX(C:C,MATCH(1,(N3=FY11RDaysOut)*(J3=FY11Commander),0)),"")

seems to work. Why do you think it doesn't?

[Huddle]

I get an #N/A error when I do it with either the range names or directly.

[NBVC]

Do you mean in every other cell starting at O4?

if so, it is because there is nothing in column J at those rows...

If you want to use the J value in cell above, then try:

=IF(N3>0,INDEX(C:C,MATCH(1,(N3=FY11RDaysOut)*(LOOKUP(REPT("z",255),J$3:J3)=FY11Commander),0)),"")

in O3, copied down.

You will still get the #N/A on every other cell, and that is because there are actually no matches... unless you can show me that there is.

[Huddle]

I'm just trying to get the formula to work for O11. Once I get it to work I can adapt the other rows.

As the example shows (pink highlight) there's a #N/A error. The answer should be UT1369found at cell C110.

I haven't use INDEX MATCH before and adapted the formula from another example I found. Maybe I'm using the wrong formula type completely.

[NBVC]

Ok, I see the issue, your named ranges in the formula are whole columns, you can't have whole column references with Array formulas in Excel 2003...

So you should then redefine the named ranges to a limited number of cells or use dynamic named ranges (i.e. change the Refers to field for FY11Commander to:

=OFFSET('Work Orders for FY11'!$H$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)

and change FY11RDaysOut to:

=OFFSET('Work Orders for FY11'!$F$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)

[Huddle]

That would make the whole formula

=IF(N11>0,INDEX(C:C,MATCH(1,(N11=OFFSET('Work Orders for FY11'!$F$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)*(J11=OFFSET('Work Orders for FY11'!$H$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)),0)),""))

It's telling me it is invalid. I'm sure there's a comma or something out of place. Do you see it?

[NBVC]

No, I mean keep the same formula, but go to Insert|Name|Define and change the refers to field for the respective named ranges as above.

See attached.

[Huddle]

Sorry I misunderstood.

The problem with that is I have other things in my workbook that reference the Commander and FY11RDaysOut range names. Changing the refers would mess up other things wouldn't it?

Is there another way?

[NBVC]

It shouldn't affect it if you change all the named ranges similarly, so that they end up all being the same size.

All you have to do is copy the above formula:

=OFFSET('Work Orders for FY11'!$H$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)

replace other named range with the formula by pasting in the refers to box.. and just change the $H$1 to reference the top cell of the column the named range is for...

The range size is dependent on column B (which seems to be the column with all cells filled)...

[Huddle]

I reviewing your Copy 2 of Example I notice that cell O11 doesn't have the answer I need. It should be UT1369.

My boss actually now wants me to reference the work order number rather than the building number but the same problem of it entering the first matching number rather than the first one under the commander will still exist.

I may need to figure out another way to accomplish this.

Thank you for your help

[NBVC]

It is the C:C after the INDEX causing that...

Create a new named range like:

FY11Bldgs with refers to formula:

=OFFSET('Work Orders for FY11'!$C$1,1,0,COUNTA('Work Orders for FY11'!$B:$B)-1,1)

Then change formula in O3 to:

=IF(N3>0,INDEX(FY11Bldgs,MATCH(1,(N3=FY11RDaysOut)*(J3=FY11Commander),0)),"")

confirmed with CTRL+SHIFT+ENTER.

Note: If you don't like the CSE entering use this formula instead in O3:

=IF(N3>0,INDEX(FY11Bldgs,MATCH(1,INDEX((N3=FY11RDaysOut)*(J3=FY11Commander),0),0)),"")

and just copy down.

Also, if you are returning Work Order number do similarly a new named range with Offset formula for column B....

[Huddle]

I went back to the original formula and referenced the work order numbers and it worked.

=INDEX($B$2:$B$999,MATCH(1,(J11=$H$2:$H$999)*(N11=$F$2:$F$999),0))

Thanks again

[NBVC]

Yes, you defined fixed ranges instead of whole columns.. that was the issue from the start.... the Dynamic named ranges allow you to have a automatically expanding/contracting range based on number of entries in your database. With array formulas, this is usually ideal, because array formulas are generally not efficient and the less cells you use the better (even blank ones) ...