# Index match for # and date- only non blank cells

#### ShalDRH

##### New Member
Hello! I have a set of month dates across a row and values filled in below for the months when activity occurs, blank when no activity occurs.

I need to look across multiple year spans and create a table to give me each date with a value in a nice little compact view. I've spent a couple of hours trying different solutions and reading other posts and I am ready to admit that I'm not going to be able to self serve! I get it to sorta work in a basic sample but not with real-life data. I don't understand it well enough to find where it is going wrong.

Examples below-

Works in theory to give me the first month and first value, second month and second value etc. However I think it just "happens" to work based on the sample data values I chose, not because the formulas are correct.

Date formula- =IFNA(INDEX(E2:M2,MATCH(TRUE,INDEX(E4:M4>0,0),0)),"")​
second date becomes =IFNA(INDEX(E2:M2,MATCH(TRUE,INDEX(E4:M4-1>0,0),0)),"") with increasing the subtraction part in red for each date field.​
Value # formula- =IFNA(INDEX(E4:Q4,SMALL(IF(E4:Q4<>"",COLUMN(E4:Q4)-COLUMN(E4)+1),1)),"")​
second value 3 becomes =IFNA(INDEX(E4:Q4,SMALL(IF(E4:Q4<>"",COLUMN(E4:Q4)-COLUMN(E4)+1),2)),"") with increasing the last # in red for each # field​

Tried with more realistic values below and it does not like my date formula. I guess I don't understand the -1, -2, etc piece. I thought it was telling it to go to the next non-blank... But it finds January as the answer every time.

The only other issue I have is why take #5 & #6 = #NUM error. Is it because there isn't a 5th or 6th value? It's saying it can't find a # but the IFNA part doesn't work because the error isn't NA????

Greatly appreciate any help and advice this group can give!

-S

#### Attachments

• 1627503009105.png
21.1 KB · Views: 3
• 1627503315754.png
21.5 KB · Views: 5

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

Maybe something like this

Pasta3
ABCDEFGHIJKL
1
2Take 1 DateJanJanFebMarchAprilMayJuneJulySept
3Take 1 #20
4Take 2 DateApril20,0025,003,0020,00
5Take 2 #25
6Take 3 DateJune
7Take 3 #3
8Take 4 DateSept
9Take 4 #20
10Take 5 Date
11Take 5 #
12Take 6 Date
13Take 6 #
Plan7
Cell Formulas
RangeFormula
C2:C13C2=IFERROR(INDEX(IF(ISNUMBER(SEARCH("Date",B2)),E\$2:M\$2,E\$4:M\$4),AGGREGATE(15,6,(COLUMN(E\$2:M\$2)-COLUMN(E\$2)+1)/(E\$4:M\$4<>""),INT((ROWS(C\$2:C2)-1)/2)+1)),"")

Hope this helps

M.

#### Marcelo Branco

##### MrExcel MVP
A picture is not helpful - is not possible to copy/paste your data for testing purposes.

Next time, to post a data sample try

M.

#### ShalDRH

##### New Member
T
Welcome to Mr Excel forum

Maybe something like this

Pasta3
ABCDEFGHIJKL
1
2Take 1 DateJanJanFebMarchAprilMayJuneJulySept
3Take 1 #20
4Take 2 DateApril20,0025,003,0020,00
5Take 2 #25
6Take 3 DateJune
7Take 3 #3
8Take 4 DateSept
9Take 4 #20
10Take 5 Date
11Take 5 #
12Take 6 Date
13Take 6 #
Plan7
Cell Formulas
RangeFormula
C2:C13C2=IFERROR(INDEX(IF(ISNUMBER(SEARCH("Date",B2)),E\$2:M\$2,E\$4:M\$4),AGGREGATE(15,6,(COLUMN(E\$2:M\$2)-COLUMN(E\$2)+1)/(E\$4:M\$4<>""),INT((ROWS(C\$2:C2)-1)/2)+1)),"")

Hope this helps

M.

This is excellent! Worked perfectly, Thank you so much for your quick reply. And I saw your second message. Thanks for the tip there and I will be sure to do that next time I need assistance!

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

Replies
3
Views
164
Replies
7
Views
152
Replies
5
Views
116
Replies
3
Views
680
Replies
11
Views
450

1,147,568
Messages
5,741,882
Members
423,693
Latest member
Excelquestion35

### 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.

### Which adblocker are you using?

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

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