Forum Discussion
Have cells output closest value in next column and diagonally populate a row?
Hi Kevin, I have put together the formula for you with OFFSET() and LOOKUP(). Please have a look at the attached document to see if it fits for you.
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,0,LOOKUP(2,1/(A1:D1<>""),COLUMN(A1:D1))-2,ROW())<>0),OFFSET($A$1,0,LOOKUP(2,1/(A1:D1<>""),COLUMN(A1:D1))-2,ROW())),"Next Column Value")
Hi Kevin, I have put together the formula for you with OFFSET() and LOOKUP(). Please have a look at the attached document to see if it fits for you.
=IFERROR(LOOKUP(2,1/(OFFSET($A$1,0,LOOKUP(2,1/(A1:D1<>""),COLUMN(A1:D1))-2,ROW())<>0),OFFSET($A$1,0,LOOKUP(2,1/(A1:D1<>""),COLUMN(A1:D1))-2,ROW())),"Next Column Value")
Hey! First of all thank you! Although It seems for some reason when I attempt to copy and paste the function it does not seem to work? It seems to just copy the value in that cell that I copy the function of even if the function is looking for a different row of cells.
A 123 Next Column Value A
B 124 Want to Output --> A Blank Blank 124 B
C 125 Want to Output --> B Blank Blank 125 B
D 126 Want to Output --> C Blank Blank 126 D
E 127 Want to Output --> C Blank Blank 127 E
F 128 Want to Output --> C Blank Blank 128 F
G 129 Want to Output --> A Blank Blank 129 G
H Wanted to Outout ----> G but got A (Copied function above and it gave me A instead of G from the excel sheet I got from you?)
Hi Kevin, the formula is not the same for every row. The example I wrote down for you here on the website was for row 1. To make the formula available for other rows, you have to copy one of the formulas inside Excel and paste it below. The formula is created in such a way that it can be copied down automatically. Try it and it will work.
- This worked! Thank you so much for the assistance! I really appreciate!