Index, Match, Return non-blank cell

F

FlexYourData

New Member
Joined
Jun 28, 2016
Messages
4
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:

[TABLE="width: 575"]
<tbody>[TR]
[TD]Excel 2012
ABCDEF
#VALUE!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]ID (Auto-fill)[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Start date (Auto-fill)[/TD]
[TD="align: center"]Step 4 or 5[/TD]
[TD="bgcolor: #D9D9D9, align: center"]# of Days After start date[/TD]
[TD="align: center"]Comments[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #A6A6A6, align: right"]1[/TD]
[TD="bgcolor: #A6A6A6"]Example[/TD]
[TD="bgcolor: #A6A6A6"]3/20/14[/TD]

[TD="bgcolor: #D9D9D9, align: right"]#VALUE![/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #A6A6A6, align: right"]2[/TD]
[TD="bgcolor: #A6A6A6"]Example2[/TD]
[TD="bgcolor: #A6A6A6"]4/11/15[/TD]

[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
3.Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]='1.Identifying Information'!B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]='2.Timeline'!C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=-(C2-D2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=MATCH(B2,'2.Timeline'!A2:M14,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]='2.Timeline'!C3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
2.Timeline:

Excel 2012
BCDEFGHIJKLM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #A6A6A6, align: center"]ID (Auto-fill)[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Start Date (Auto-fill)[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Step 1 (mandatory)[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]Step 2 (mandatory)[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]Step 3 (mandatory)[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]Step 4[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]Step 5[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #A6A6A6"]Example[/TD]
[TD="bgcolor: #A6A6A6"]3/20/2014[/TD]
[TD="align: right"]3/20/14[/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"]3/25/14[/TD]
[TD="bgcolor: #D9D9D9, align: right"]5[/TD]
[TD="align: right"]3/25/14[/TD]
[TD="bgcolor: #D9D9D9, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]###[/TD]
[TD="align: right"]4/10/14[/TD]
[TD="bgcolor: #D9D9D9, align: right"]21[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #A6A6A6"]Example 2[/TD]
[TD="bgcolor: #A6A6A6"]4/11/2015[/TD]
[TD="align: right"]4/15/15[/TD]
[TD="bgcolor: #D9D9D9, align: right"]4[/TD]
[TD="align: right"]4/30/15[/TD]
[TD="bgcolor: #D9D9D9, align: right"]19[/TD]
[TD="align: right"]5/18/15[/TD]
[TD="bgcolor: #D9D9D9, align: right"]37[/TD]
[TD="align: right"]6/1/15[/TD]
[TD="bgcolor: #D9D9D9, align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]###[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #A6A6A6"]Example 3[/TD]
[TD="bgcolor: #A6A6A6"]1/0/1900[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #A6A6A6"]Example 4[/TD]
[TD="bgcolor: #A6A6A6"]1/0/1900[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #A6A6A6"]Example 5[/TD]
[TD="bgcolor: #A6A6A6"]1/0/1900[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]

</tbody>
2.Timeline​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]='1.Identifying Information'!B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]='1.Identifying Information'!C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]='1.Identifying Information'!C3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]='1.Identifying Information'!C4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]='1.Identifying Information'!C5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]='1.Identifying Information'!C6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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)
 
Upvote 0
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
BCDEF
4/10/2014
#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #A6A6A6, align: center"]ID (Auto-fill)[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Start date (Auto-fill)[/TD]
[TD="align: center"]Step 4 or 5[/TD]
[TD="bgcolor: #D9D9D9, align: center"]# of Days After start date[/TD]
[TD="align: center"]Comments[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #A6A6A6"]Example[/TD]
[TD="bgcolor: #A6A6A6"]3/20/14[/TD]

[TD="bgcolor: #D9D9D9, align: right"]21[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #A6A6A6"]Example2[/TD]
[TD="bgcolor: #A6A6A6"]4/11/15[/TD]

[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
3.Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]='1.Identifying Information'!B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]='2.Timeline'!C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=-(C2-D2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=MATCH(B2,'2.Timeline'!A2:M14,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]='2.Timeline'!C3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=INDEX('2.Timeline'!L2:'2.Timeline'!L250,MATCH(B2&C2,'2.Timeline'!B2:'2.Timeline'!B250&'2.Timeline'!C2:'2.Timeline'!C250,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=INDEX('2.Timeline'!L3:'2.Timeline'!L251,MATCH(B3&C3,'2.Timeline'!B3:'2.Timeline'!B251&'2.Timeline'!C3:'2.Timeline'!C251,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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!


Irrelevant said:
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)
Click to expand...
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?

Irrelevant said:
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.
Click to expand...
 
Upvote 0
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.
 
Upvote 0
That does the trick!! Thank you very much for your help!

Irrelevant said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
566
wsr429
W
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
366
Alex Blakenburg
A
C
  • Solved
index match excel formula to vba code
Replies
9
Views
319
DanteAmor
DanteAmor
D
  • Solved
Exact Match with ISNUMBER(SEARCH)
Replies
2
Views
185
danhenshy23
D
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
279
stuartgb100
S

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top