Problem with Index Match

[olympiac]

Scenario:
Table in sheet(1) has got rows with the following headings
A1=System ; B1=Name; C1=status

Table in Sheet(2) has got a matrix where the systems are on the top(columns) and the names are on the left side(rows)

I need a formula in sheet(2) that gives me in the intersection between the system and the name the status from sheet(1)

 

[Aladin Akyurek]

Scenario:
Table in sheet(1) has got rows with the following headings
A1=System ; B1=Name; C1=status

Table in Sheet(2) has got a matrix where the systems are on the top(columns) and the names are on the left side(rows)

I need a formula in sheet(2) that gives me in the intersection between the system and the name the status from sheet(1)

Sheet2, B2...

Control+shift+enter, not just enter, and copy across then down:

=INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0))

where B1 is a system and A2 a name, as you specified.

 

[olympiac]

Great - it works. Thanks

 

[Aladin Akyurek]

Great - it works. Thanks

You are welcome. Thanks for providing feedback.

 

[olympiac]

Please - help.

The table in Sheet(2) is already populated with a formula (Eg. =H3) that gives a status.
The way it works is that the status in Sheet(2) will have to be updated if the formula that you provided me is not N/A. If it's N/A then then Nothing (the result will be the one from cell H3).
I need to integrate your formula with a "=IF(ISERROR".
I tried this

=IF(ISERROR(INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0))),H3,INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0)))

I split the formula for better understanding:

=IF(ISERROR(
=INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0)))
,H3,
=INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0)))

What's wrong with the formula? I get the value from cell H6 everywhere.

 

[Aladin Akyurek]

Please - help.

The table in Sheet(2) is already populated with a formula (Eg. =H3) that gives a status.
The way it works is that the status in Sheet(2) will have to be updated if the formula that you provided me is not N/A. If it's N/A then then Nothing (the result will be the one from cell H3).
I need to integrate your formula with a "=IF(ISERROR".
I tried this

=IF(ISERROR(INDEX(Sheet1!$C$2:$C$10,
MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0))),H3,INDEX(Sheet1!$C$2:$C$10,
MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,IF(Sheet1!$B$2:$B$10=$A2,1)),0)))

I split the formula for better understanding:

=IF(ISERROR(
=INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,
IF(Sheet1!$B$2:$B$10=$A2,1)),0)))
,H3,
=INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,
IF(Sheet1!$B$2:$B$10=$A2,1)),0)))

What's wrong with the formula? I get the value from cell H6 everywhere.

Let B2 house the formula and H2 the previous status value. Having this, we can invoke:

Control+shift+enter, not just enter:

=LOOKUP(REPT("z",255),CHOOSE({1,2},
   INDEX(Sheet1!$C$2:$C$10,MATCH(1,IF(Sheet1!$A$2:$A$10=B$1,
    IF(Sheet1!$B$2:$B$10=$A2,1)),0))),$H2))

Does this cover your intent?

Note that the status value is expected to be text.