Referencing a non-integer or generating a consecutive integer

Hi all, first time posting here so go easy please. I've searched around for the type of formula I think I need, but I can't seem to either search for the right phrasing or I might have an incorrect idea of going about it. Basically this is the setup:

I have two sheets (Sheet1, Sheet2).

Sheet 1 contains 2 columns of information/numbers (1.1 and 1.2)

Sheet 2 contains the 1 column I'm interested in having the formula (2.1).

In columns 1.1 and 1.2, I have integers as well as non-integers (rational and irrational numbers). These integers and non-integers are in a semi-continuous series (always increasing, never decreasing). An example would be 1, 4, 7, 7.8, 10, 12.35, 15, etc. with column 1.2 being a staggered version of column 1.1 (in place of 1, it starts with 4, then 7, 7.8, 10, 12.35, 15, etc.).

In column 2.1, I need a formula that will examine the cell above the cell containing the formula (say the formula is in A3, have it examine A2) and compare A2's content to columns 1.1 and 1.2. If it can find the number, have it +1 to A2's content, if not, have it compare A2 to A1+1. If it is true, then again +1 to A2's content. But I also need it to somehow fill in the gaps of integers and recognize when there is a non-integer close to A2's content to substitute the non-integer into the cell, i.e. from 1 to 4 the formula need to fill in for 2 and 3 in the cells.

Ideally A1=1, then A2 should = 2, A3=3, A4=4, etc until you hit 7.8. I need A8=7.8 somehow, and then for it to resume the "integer filling" with A9=8, A10=9, A11=10, etc. until it hits 12.35 where it pulls that instead (A14=12.35).

I'm trying an argument with the IF, ISNUMBER, and MATCH functions, but I'm not sure how to do the "integer filling" and non-integer referencing. I either end up with none of the non-integers being referenced (A8 would just be 8, A14 would just be 14, etc) or I end up with a reference to the relative cell location (A8 = Sheet1!A8 which is not what I need) without the "integer filling" between cells.

I know this all sounds very convoluted, but I've attached an example. I'm trying to use this formula for a large group of data (say 120 - 180 different integers and non-integers), at least it's large to me. Any insight would be greatly appreciated.

jocund.ky,

Attached is a modified version of your sample workbook. I left Sheet2 column A alone so that I could have it for reference and used column B for the formula. The formula's a bit long and ugly because its checking both column A and B in Sheet1. With the "staggered" setup in Sheet1, I don't see why this is necessary, but here's the result. Starting in Sheet2 cell B2 is:

Please Login or Register  to view this content.

And then its copied down. Is something like that what you're looking for?