# How to return first non-blank cell and if it's already a value before it, return the third non-blank and so on?

How to make a function which would return the first non-blank cell value but if it's defined in the left cells next to it, it would jump 2 cells and look for the next value.

``````Table:
1 | 2 | 3 | 4 | 5 | 6 | 7 || 29 | 30 | 31 | 32...
a | b |   |   | c | d |   || a  | c  |    |
|   |   | a | b |   |   || a  |    |    |
a | b | c | d | e | f | g || a  | c  | e  | g
``````

from DL3:EW4873 is the values, always in pairs, but I need only the first value-piece and only if it's not already a value in the EX to EK range.

Also the EX:FK cells are the place where formulas will go.

-
Do you want to do this for each row? You can find first text value in row 3 with this formula `=INDEX(AO3:EU3,MATCH("*",AO3:EU3,0))` but that doesn't address the second part, do you mean you want to find the first value that appears that isn't in EK3:EX3? – barry houdini Feb 21 '12 at 11:13
Yes for each row and that doesn't appear in EK3:EX3. – Tusk Feb 21 '12 at 11:21

OK don't those ranges overlap? In any case try this version, adjust ranges as required

`=INDEX(AO3:EU3,MATCH(1,INDEX(ISNA(MATCH(AO3:EU3,EK3:EX3,0))*(MOD(COLUMN(AO3:EU3)-COLUMN(D3),2)=0)*(AO3:EU3<>""),0),0))`

That looks for the first non-blank value in alternate cells, AO3, AQ3, AS3 etc. that doesn't match any value found in EK3:EX3

Revised suggestion:

OK based on your comments, I'm assuming that you still want non-blank data from alternate cells so with data in AO3:EI3 and with EJ3 blank try this formula in EK3 copied across to get the next different value

`=IFERROR(INDEX(\$AO3:\$EH3,MATCH(1,INDEX((COUNTIF(\$EJ3:EJ3,\$AO3:\$EH3)=0)*(MOD(COLUMN(\$AO3:\$EH3)-COLUMN(\$AO3),2)=0)*(\$AO3:\$EH3<>""),0),0)),"")`

when values run out you get blanks.....

Revised suggestion 2:

Try this formula in EX3 only

`=IFERROR(INDEX(\$DL3:\$EW3,MATCH(1,INDEX((MOD(COLUMN(\$DL3:\$EW3)-COLUMN(\$DL3),2)=0)*(\$DL3:\$EW3<>""),0),0)),"")`

and then this one in EY3 copied across

`=IFERROR(INDEX(\$DL3:\$EW3,MATCH(1,INDEX((COUNTIF(\$EX3:EX3,\$DL3:\$EW3)=0)*(MOD(COLUMN(\$DL3:\$EW3)-COLUMN(\$DL3),2)=0)*(\$DL3:\$EW3<>""),0),0)),"")`

Revised version for "second pair" - this should just pick up the associated values from the first formula

`=IFERROR(INDEX(\$DL3:\$EW3,MATCH(1,INDEX((MOD(COLUMN(\$DL3:\$EW3)-COLUMN(\$DL3),2)=0)*(\$DL3:\$EW3<>""),0),0)+1)&"","")`

and this one in EY3 copied across

`=IFERROR(INDEX(\$DL3:\$EW3,MATCH(1,INDEX((COUNTIF(\$EX3:EX3,\$DL3:\$EW3)=0)*(MOD(COLUMN(\$DL3:\$EW3)-COLUMN(\$DL3),2)=0)*(\$DL3:\$EW3<>""),0),0)+1)&"","")`

change the `\$EX3:EX3` part depending on location of formulas.....

-
OK, I must be missing something, where are you putting the formula? I assumed it would only go once in each row, and not in any cells that are referenced by the formula – barry houdini Feb 21 '12 at 12:22
@Tusk OK, I understand, I added a revised formula above – barry houdini Feb 21 '12 at 15:12
OK, I was hoping you would have a blank cell between the data range and your formulas, if not then I think you need a slightly different formula for the first cell, see Revised suggestion 2 above – barry houdini Feb 22 '12 at 9:37
Just change the range so that it starts with the second pair, i.e. change instances of `\$DL3:\$EW3` to `\$DM3:\$EW3`....and where there is `\$DL3` on it's own that should become `\$DM3`..... – barry houdini Feb 23 '12 at 0:01
Works OK for me :).....I realise you can't have both lots of formulas in the same cells but assuming you want the "second pair" version in EX3 across see revised answer above....... – barry houdini Feb 23 '12 at 13:09