INDEX Function

[DM2]

I'm having a heck of a time trying to figure out how to use the INDEX function.

I have "TABLE 1" that lists:
- INDEX NUMBER
- AREA (defined as "100-1", "110-1", etc.)
- DEVICE (example number: 5-HS-6163)
- LOC_ALARM (example: 40144/1, "LOC" is short for "memory Location")
- LOC_FAULT (example: 40144/2)
- LOC_STOP (example: 40144/3)

Table 1 is completely filled out and there will never be a the same DEVICE in two AREA's, but each DEVICE has an "ALARM_LOC", "FAULT_LOC" and "STOP_LOC".

Table 2 is divided into 5 groups, with each group associated to a specific "AREA". The goal is to find the DEVICE in table 1, for a specific "LOC_ ..." value, and capture the DEVICE number, and which one of the 3 possible "LOC_..." values it is.

I've tried a number of different variations of the INDEX function with a MATCH function, but I can't seem to get it to work. Anyone's help is greatly appreciated.

The attached spreadsheet show the two tables.

[MarvinP]

Hi Dan,

I did the Orange cells. See my example and see if you can do the rest using what I've done.

[DonkeyOte]

@DM2, in reality, is Table1 as uniform as implied by the sample ?

For ex. each area will always contain the same number of rows and the LOC_ values will always repeat per area ?

If not I would suggest posting a revised sample to reflect oddities as these will largely dictate the most appropriate approach to your problem.

If (big If) the Tables and data are exactly as outlined then:

Please Login or Register  to view this content.

[DM2]

MarvinP,
Thanks for your input.

DonkeyOte,
A couple of comments about "Table 1"
- Not all of the devices have a "LOC_..." value assigned to them.
- You'll note that value "XXXXX/16" is never used
- There is the possibility that the same device would also be in another area (i.e. 3-HS-6164 may be in area "110-1" and "120-1"), but no area will have the same device. Because of this the formula for "L" doesn't seem to capture what I need.

In the case of a "LOC_..." value that isn't being used, is there a simpler way to have column "I" = "", rather than add a "IF(ISNA(...),"",..." equation?

I though I might have to use an "INDEX" function nested in the "MATCH" function but wasn't sure how to do it. I though I might have to do it to establish a dynamic range based on the AREA name.

Everyone's help is greatly appreciated. I've attached a revised spreadsheet that reflects the anomalies mentioned above.

[DonkeyOte]

@DM2,

Apologies for belated response, this went under my radar.

If still required: see if the attached is along the right lines...

[DM2]

This does the trick...

Now...I can never remember how to change the message to SOLVED?

[MarvinP]

Click the green EDIT button below the original post. Find the Prefix to the thread title and change it to solved.

[DM2]

Oh...You've got to go "Advanced" to get that option...

Thanks MarvinP