Index, Match, Return non-blank cell

[FlexYourData]

Hello! I am trying to use the Index Match functions to carry over data from one sheet to another. I am a little lost as to how to make this work, so I am not sure if what I have is on the right track.

Goal:
In the sheet titled 3.Summary (first screen shot below) the formula in cell D2 is trying to bring over the date from column J or L within sheet 2.Timeline (second screens shot below). The value used to match between the two sheets is a concatenation of ID (column B) and Start date (column C). The value that should appear in cell D2 is 4/10/14 and the value that should appear in E2 is 6/1/15.

3.Summary:

Excel 2012 A B C D E F 1 ID (Auto-fill) Start date (Auto-fill) Step 4 or 5 # of Days After start date Comments 2 1 Example 3/20/14 #VALUE! #VALUE! #N/A 3 2 Example2 4/11/15 <colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead> </thead><tbody> </tbody> 3.Summary​ Worksheet Formulas Cell Formula B2 ='1.Identifying Information'!B2 C2 ='2.Timeline'!C2 D2 =INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0)) E2 =-(C2-D2) F2 =MATCH(B2,'2.Timeline'!A2:M14,0) C3 ='2.Timeline'!C3 <thead> </thead><tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

2.Timeline:

Excel 2012

	B	C	D	E	F	G	H	I	J	K	L	M
1	ID (Auto-fill)	Start Date (Auto-fill)	Step 1 (mandatory)		Step 2 (mandatory)		Step 3 (mandatory)		Step 4		Step 5
2	Example	3/20/2014	3/20/14	0	3/25/14	5	3/25/14	5		###	4/10/14	21
3	Example 2	4/11/2015	4/15/15	4	4/30/15	19	5/18/15	37	6/1/15	51		###
4	Example 3	1/0/1900		0		0		0		0		0
5	Example 4	1/0/1900		0		0		0		0		0
6	Example 5	1/0/1900		0		0		0		0		0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

2.Timeline​

Worksheet Formulas Cell Formula B2 ='1.Identifying Information'!B2 C2 ='1.Identifying Information'!C2 C3 ='1.Identifying Information'!C3 C4 ='1.Identifying Information'!C4 C5 ='1.Identifying Information'!C5 C6 ='1.Identifying Information'!C6 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Any help you can provide is greatly appreciate! Thank you!

 

[Irrelevant]

Try ctrl+shift+enter on cell D2, should do the trick i think so it should just be the same formula but appear as :{=INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))}

(make sure it gets the { } brackets once you do this, don't type it in)

 

[FlexYourData]

Thank you very much for your response! It works when I add the curly brackets, but if I copy the formula down to the next row I get #N/A. The copied formula still has the curly brackets so I'm not sure why it's not working.

Excel 2012

	B	C	D	E	F
1	ID (Auto-fill)	Start date (Auto-fill)	Step 4 or 5	# of Days After start date	Comments
2	Example	3/20/14	4/10/2014	21	#N/A
3	Example2	4/11/15	#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

3.Summary​

Worksheet Formulas Cell Formula B2 ='1.Identifying Information'!B2 C2 ='2.Timeline'!C2 E2 =-(C2-D2) F2 =MATCH(B2,'2.Timeline'!A2:M14,0) C3 ='2.Timeline'!C3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))} D3 {=INDEX('2.Timeline'!L3:'2.Timeline'!L251,MATCH(B3&C3,'2.Timeline'!B3:'2.Timeline'!B251&'2.Timeline'!C3:'2.Timeline'!C251,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The other piece I can't figure out is how to adjust the formula so that it will return either column J or column L, depending on which is not blank (from sheet 2.Timeline). Do you know how I can include this logic in the formula I have?

Thanks again!

Try ctrl+shift+enter on cell D2, should do the trick i think so it should just be the same formula but appear as :{=INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))}

(make sure it gets the { } brackets once you do this, don't type it in)

 

[Irrelevant]

In this case, it looks like the "ID column in 1 sheet has a different value structure as the other sheet. I see one seems to have "example2" no space, the other looks like example 2 with a space. The #N/A just means it didn't find a match.

But you also need to create absolute references for all the ranges that shouldn't change so your formula refers to the full ranges from top always. you can do that by manually typing $ sign before the column and row indicator, or you could cycle to that by pressing f4 as you edit the formula @ each range.
https://support.office.com/en-us/art...e-5f0d8d0baca9

Once absolute, copying the formula won't change anything.

 

[FlexYourData]

Thanks again! I should have caught that but thanks for pointing it out.

I am new to posting questions to the discussion boards. Would you recommend I separate the second part of my question (adjusting the formula to return the value in either column J or L depending which is not blank) to a new thread?

In this case, it looks like the "ID column in 1 sheet has a different value structure as the other sheet. I see one seems to have "example2" no space, the other looks like example 2 with a space. The #N/A just means it didn't find a match.

But you also need to create absolute references for all the ranges that shouldn't change so your formula refers to the full ranges from top always. you can do that by manually typing $ sign before the column and row indicator, or you could cycle to that by pressing f4 as you edit the formula @ each range.
https://support.office.com/en-us/art...e-5f0d8d0baca9

Once absolute, copying the formula won't change anything.

 

[Irrelevant]

Oh didn't notice that part of question- and i'm new too so can't say -- but in this case, assuming the values in J and L are mutually exclusive (one will always be empty), the simplest thing here would be just to add them together.
Adding together only works if they are recognized as dates -- if you are dealing with text, or if they are not mutually exclusive, you will likely need to do an if statement where you check the value returned from column L, if that is non-zero, then use it, otherwise retrieve the value from column L.

So maybe {=if(INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))>0,INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0)),INDEX('2.Timeline'!J2:'2.Timeline'!J250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0)))}

I'm sure there might be a more efficient way to right this but that should work.

 

[FlexYourData]

That does the trick!! Thank you very much for your help!

Oh didn't notice that part of question- and i'm new too so can't say -- but in this case, assuming the values in J and L are mutually exclusive (one will always be empty), the simplest thing here would be just to add them together.
Adding together only works if they are recognized as dates -- if you are dealing with text, or if they are not mutually exclusive, you will likely need to do an if statement where you check the value returned from column L, if that is non-zero, then use it, otherwise retrieve the value from column L.

So maybe {=if(INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))>0,INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0)),INDEX('2.Timeline'!J2:'2.Timeline'!J250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0)))}

I'm sure there might be a more efficient way to right this but that should work.