Forum Discussion
Solved
Have cells output closest value in next column and diagonally populate a row?
Hello everyone, I hope you are all doing well.
I want to make a spreadsheet that puts the values of a column into a specific row much like below.
| A | 123 | Next Column Value | ||||||||
| B | 124 | Want to Output --> | A | Blank | Blank | 124 | B | |||
| C | 125 | Want to Output --> | B | Blank | Blank | 125 | C | |||
| 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 |
Trying to essentially have it look in the next column over to find the closest value above and populate it in the row of the value respective to it. Hopefully this make senses. Not sure where to even start with this. Thank you!
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.
