Formula to match cells and find information

[randallshoe]

Please see the attached image. I have model names in sheet 1 and sheet 2. Sheet 1 also shows that the models have been drawn. I would like to have a formula or vba code that says, "IF a cell from CHARACTERISTICS!A:A matches a cell from ROOM USAGE!A:A and the corresponding row from CHARACTERISTICS!B:B has an "X" in the cell then it should fill the cell in ROOM USAGE!D:D with an "X".

Can anyone help me with this? I have tried using MATCH, VLOOKUP, IF, AND, etc.

Ideally I would like this to be conditional formatting so that it gives a color to ROOM USAGE!A:A.

[/IMG]

 

[wideboydixon]

Formula for D2 on ROOM USAGE would be:

=IF(IFERROR(INDEX(CHARACTERISTICS!$B:$B,MATCH($A2,CHARACTERISTICS!$A:$A,0)),"")="X","X","")

You could make this into a conditional format on that tab using the same approach. Highlight the appropriate cells from A2 downwards in ROOM USAGE and base a new conditional format on the following formula:

=IFERROR(INDEX(CHARACTERISTICS!$B:$B,MATCH($A2,CHARACTERISTICS!$A:$A,0)),"")="X"

WBD

 

[randallshoe]

I knew there was a way. Thank you!