Looking at second Index Match if first is blank

[ssidhu8]

Hello All,

Been looking all over for this answer but am yet to still figure it out.
Since index match only looks at the first match, and if it is blank it will return a 0, how do I get excel to look at the second match which may contain information?

Here is the data I am working with and the current formula I have:

Sheet 1
[TABLE="width: 654"]
<tbody>[TR]
[TD]A
PM
[/TD]
[TD]B
Area
[/TD]
[TD]C
Project ID
[/TD]
[TD]D
Project Name
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA
[/TD]
[TD]P.05780
[/TD]
[TD]Martin Install
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA
[/TD]
[TD]P.05780
[/TD]
[TD]Martin Install
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA
[/TD]
[TD]P.05780
[/TD]
[TD]Martin Install
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA
[/TD]
[TD]P.05780
[/TD]
[TD]Martin Install
[/TD]
[/TR]
</tbody>[/TABLE]


The PM name I am trying to find is in sheet 2 column J
[TABLE="width: 616"]
<tbody>[TR]
[TD]D
Project #
[/TD]
[TD]E
Order
[/TD]
[TD]
F
Order Description
[/TD]
[TD]
G
Budget Center
[/TD]
[TD]
H
Budget Level 3
[/TD]
[TD]
I
Status
[/TD]
[TD]
J
PM
[/TD]
[/TR]
[TR]
[TD]P.05780
[/TD]
[TD][/TD]
[TD]Install Martin
[/TD]
[TD]Bay Area
[/TD]
[TD="colspan: 2"]Operations
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.05780
[/TD]
[TD][/TD]
[TD]Install Martin
[/TD]
[TD]Bay Area
[/TD]
[TD]Operations
[/TD]
[TD]Active
[/TD]
[TD]Salem, Rahi
[/TD]
[/TR]
</tbody>[/TABLE]

The current formula I have is :
INDEX('Sheet 2!J:J,MATCH('Sheet 1!C1,'Sheet 2!D:D,0)))

How do I get excel to look at row 2 along with row 1 to return the value of Salem, Rahi?

Thank you!

 

[Scott Huish]

I think you mean C2 not C1 because you have headers. Does this work for you?

=INDEX(Sheet2!J:J,MATCH(1,(Sheet2!D:D=Sheet1!C2)*(Sheet2!J:J<>""),0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[ssidhu8]

It did! Thank you so much Scott!!