Index Match with absolute reference from cross sheet.

Question

I asked a similar question yesterday but realized I may have asked it the wrong way.

I have two sheets. The first is a sheet with two contact tables, the first table is as follows: Division, Superintendent, Work#, Cell #. The second table is School, Principal, Work # and Cell #. There are 38 rows in the first table and over 1,000 in the second.

The second sheet consists of all this information but in a way that is easily viewable in card view ( I don't think the structure is very relevant in the second sheet, but please correct me if I'm wrong). Each card shows the school - the superintendent and the principal, one superintendent could be listed for 30-50 schools.

What i'm trying to do is use the first sheet as the reference sheet.

Rather than VLookup, I am using an Index Match: =INDEX({Divison Contact Table_Update_Here Range 17}, MATCH({Divison Contact Table_Update_Here Range 19}, {Divison Contact Table_Update_Here Range 19}, 0))

What i'm finding though, is if I drag the formula down, it does not take into account the change of division/school. I believe I need to use an absolute reference, but I have no idea where to place the $ or @row symbols.

TIA

David Joyeuse · Answer

@Lila De Vera

The first argument in the MATCH function cannot be a range, it has to be a cell, so it must be the division cell on that row.

Could you either post screenshot with dummy data of your sheets, or share to david.joyeuse@ctdi.eu

Thanks!

Lila De Vera · Answer

Thanks @David Joyeuse.

is the [Division Contact]@row from the same sheet as the others? I can't seem to format this properly.

=INDEX({Divison Contact Table_Update_Here Range 17}, MATCH({Divison Contact Table_Update_Here Range 2}@row, {Divison Contact Table_Update_Here Range 19}, 0)), this is returning as #Unparseable.

To break this down: {Divison Contact Table_Update_Here Range 17} = the column of superintendents, {Divison Contact Table_Update_Here Range 2}@row = the division, and {Divison Contact Table_Update_Here Range 19} = the column of divisions.

David Joyeuse · Answer

Hi @Lila De Vera

Problem in your formula lies in the MATCH function:

=INDEX({Divison Contact Table_Update_Here Range 17}, MATCH({Divison Contact Table_Update_Here Range 19}, {Divison Contact Table_Update_Here Range 19}, 0))

the MATCH should be like this:

MATCH([Division Contact]@row, {Divison Contact Table_Update_Here Range 19}, 0)

Goal is to find the Division Contact on the range 19 of your second sheet, then match it on the reference sheet to return the value of the range 17 within that same row.

Hope it helped.