Help with formula please.

MrsFrankieH · Aug 1, 2019

Hello everyone,
My computer is Dell and my operating system is Windows 10.
My goal is to be able to paste numbers in cells (in this example cells D2 to H2) and have appropriate numbers populate the target cells (cells above and below colored cells). I don't know the appropriate terminology to explain so this is what the cells look like after I've used a rudimentary formula. The formula I used to populate the middle (colored) cells are D6=INT(D2/10), E6=MOD (D2,10), G6=INT(E2/10), H6=MOD(E2,10), ...etc.

On the left are the numbers used to populate the cells above and below. For example, in the first box in yellow, the number in the first yellow cell is "0", so the number above "0" would be "9" and the number below "0" would be "1". In the next yellow box over, the number is "5", so looking at the chart on the side, the number above "5" would be "6" and the number below "5" would be "4". The same process would be repeated in the the other colored cells.

I hope I was able to explain what I need help with. I don't have the terminology to explain it like I'd like to.
Thank you so much in advance.

sheetspread · Aug 1, 2019

If you don't feel like typing or pasting the formulas you can run this code on a copy of your sheet:

Code:

Sub formulapaste()
Range("D5").Value = "=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(D6,$A$2:$A$11,0)-1)),10))"
Range("E5").Value = "=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(E6,$B$2:$B$11,0)-1)),10))"
Range("D6").Value = "=INT(D2/10)"
Range("E6").Value = "=MOD(D2,10)"
Range("D7").Value = "=INDEX($A$2:$A$11,MOD(MATCH(D6,$A$2:$A$11,0),10)+1)"
Range("E7").Value = "=INDEX($B$2:$B$11,MOD(MATCH(E6,$B$2:$B$11,0),10)+1)"
Range("G5").Value = "=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(G6,$A$2:$A$11,0)-1)),10))"
Range("H5").Value = "=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(H6,$B$2:$B$11,0)-1)),10))"
Range("G6").Value = "=INT(E2/10)"
Range("H6").Value = "=MOD(E2,10)"
Range("G7").Value = "=INDEX($A$2:$A$11,MOD(MATCH(G6,$A$2:$A$11,0),10)+1)"
Range("H7").Value = "=INDEX($B$2:$B$11,MOD(MATCH(H6,$B$2:$B$11,0),10)+1)"
Range("J5").Value = "=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(J6,$A$2:$A$11,0)-1)),10))"
Range("K5").Value = "=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(K6,$B$2:$B$11,0)-1)),10))"
Range("J6").Value = "=INT(F2/10)"
Range("K6").Value = "=MOD(F2,10)"
Range("J7").Value = "=INDEX($A$2:$A$11,MOD(MATCH(J6,$A$2:$A$11,0),10)+1)"
Range("K7").Value = "=INDEX($B$2:$B$11,MOD(MATCH(K6,$B$2:$B$11,0),10)+1)"
Range("M5").Value = "=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(M6,$A$2:$A$11,0)-1)),10))"
Range("N5").Value = "=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(N6,$B$2:$B$11,0)-1)),10))"
Range("M6").Value = "=INT(G2/10)"
Range("N6").Value = "=MOD(G2,10)"
Range("M7").Value = "=INDEX($A$2:$A$11,MOD(MATCH(M6,$A$2:$A$11,0),10)+1)"
Range("N7").Value = "=INDEX($B$2:$B$11,MOD(MATCH(N6,$B$2:$B$11,0),10)+1)"
Range("P5").Value = "=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(P6,$A$2:$A$11,0)-1)),10))"
Range("Q5").Value = "=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(Q6,$B$2:$B$11,0)-1)),10))"
Range("P6").Value = "=INT(H2/10)"
Range("Q6").Value = "=MOD(H2,10)"
Range("P7").Value = "=INDEX($A$2:$A$11,MOD(MATCH(P6,$A$2:$A$11,0),10)+1)"
Range("Q7").Value = "=INDEX($B$2:$B$11,MOD(MATCH(Q6,$B$2:$B$11,0),10)+1)"
End Sub

MrsFrankieH · Aug 1, 2019

Hi Spreadsheet!! I'm going to run a repair on my Excel and try again, i think that's the problem.

MrsFrankieH · Aug 1, 2019

That looks impressive Spreadsheet!! I've never done codes before. I'm going to have to look up where to put it. Thank you so much!! It's another weapon in my arsenal.

sheetspread · Aug 1, 2019

MrsFrankieH said:
Maybe my Excel is corrupted?

Be sure everything matches exactly. There are spaces between your cells so simply dragging over will distort many of the references.

sheetspread · Aug 1, 2019

MrsFrankieH said:
I've never done codes before.

Type alt-f11, paste the code, and run (via the play button or F5 key)

Eric W · Aug 1, 2019

Looks like I was a bit hasty when I posted my original formulas. The second formulas for each color should have been reversed:

Excel 2012

	A	B	C	D	E
Up	Down


Up	Down	Up	Down	Up	Down

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=MOD(D6-1,10)[/TD]
[/TR]
[TR]
[TH]E5[/TH]
[TD="align: left"]=MOD(E6+1,10)[/TD]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]=MOD(G6-1,10)[/TD]
[/TR]
[TR]
[TH]H5[/TH]
[TD="align: left"]=MOD(H6+1,10)[/TD]
[/TR]
[TR]
[TH]J5[/TH]
[TD="align: left"]=MOD(J6-1,10)[/TD]
[/TR]
[TR]
[TH]K5[/TH]
[TD="align: left"]=MOD(K6+1,10)[/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]=MOD(D6+1,10)[/TD]
[/TR]
[TR]
[TH]E7[/TH]
[TD="align: left"]=MOD(E6-1,10)[/TD]
[/TR]
[TR]
[TH]G7[/TH]
[TD="align: left"]=MOD(G6+1,10)[/TD]
[/TR]
[TR]
[TH]H7[/TH]
[TD="align: left"]=MOD(H6-1,10)[/TD]
[/TR]
[TR]
[TH]J7[/TH]
[TD="align: left"]=MOD(J6+1,10)[/TD]
[/TR]
[TR]
[TH]K7[/TH]
[TD="align: left"]=MOD(K6-1,10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

And as far as using the lookup tables, the way I interpret your request is that you want the values surrounding the value in row 6 in the specified table. If so, try this:

Excel 2012

	A	B	C	D	E	F	G	H	I
Up	Down


Up	Down	Up	Down	Up	Down	Up	Down	Up	Down

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=INDEX($A$2:$B$11,MOD(MATCH(D6,INDEX($A$2:$B$11,0,MATCH(D$4,$A$1:$B$1,0)),0)-2,10)+1,MATCH(D$4,$A$1:$B$1,0))[/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]=INDEX($A$2:$B$11,MOD(MATCH(D6,INDEX($A$2:$B$11,0,MATCH(D$4,$A$1:$B$1,0)),0),10)+1,MATCH(D$4,$A$1:$B$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Copy the D5 formula to all the row 5 cells, and copy the D7 formula to all the row 7 cells. (I tested them all this time!)

MrsFrankieH · Aug 1, 2019

sheetspread said:
Type alt-f11, paste the code, and run (via the play button or F5 key)

Thank you Spreadsheet, I checked to make sure everything is lined up and I'm practicing with the codes. This is how i learn. Thanks again for everything!!!

MrsFrankieH · Aug 1, 2019

Eric W said:
Looks like I was a bit hasty when I posted my original formulas. The second formulas for each color should have been reversed:

Excel 2012

A B C D E F G H I J K
Up Down
Up Down Up Down Up Down

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet9

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=MOD(D6-1,10)[/TD]
[/TR]
[TR]
[TH]E5[/TH]
[TD="align: left"]=MOD(E6+1,10)[/TD]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]=MOD(G6-1,10)[/TD]
[/TR]
[TR]
[TH]H5[/TH]
[TD="align: left"]=MOD(H6+1,10)[/TD]
[/TR]
[TR]
[TH]J5[/TH]
[TD="align: left"]=MOD(J6-1,10)[/TD]
[/TR]
[TR]
[TH]K5[/TH]
[TD="align: left"]=MOD(K6+1,10)[/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]=MOD(D6+1,10)[/TD]
[/TR]
[TR]
[TH]E7[/TH]
[TD="align: left"]=MOD(E6-1,10)[/TD]
[/TR]
[TR]
[TH]G7[/TH]
[TD="align: left"]=MOD(G6+1,10)[/TD]
[/TR]
[TR]
[TH]H7[/TH]
[TD="align: left"]=MOD(H6-1,10)[/TD]
[/TR]
[TR]
[TH]J7[/TH]
[TD="align: left"]=MOD(J6+1,10)[/TD]
[/TR]
[TR]
[TH]K7[/TH]
[TD="align: left"]=MOD(K6-1,10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

And as far as using the lookup tables, the way I interpret your request is that you want the values surrounding the value in row 6 in the specified table. If so, try this:

Excel 2012

A B C D E F G H I J K L M N O P Q
Up Down
Up Down Up Down Up Down Up Down Up Down

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet9

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=INDEX($A$2:$B$11,MOD(MATCH(D6,INDEX($A$2:$B$11,0,MATCH(D$4,$A$1:$B$1,0)),0)-2,10)+1,MATCH(D$4,$A$1:$B$1,0))[/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]=INDEX($A$2:$B$11,MOD(MATCH(D6,INDEX($A$2:$B$11,0,MATCH(D$4,$A$1:$B$1,0)),0),10)+1,MATCH(D$4,$A$1:$B$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Copy the D5 formula to all the row 5 cells, and copy the D7 formula to all the row 7 cells. (I tested them all this time!)

Thank you Eric!!! I really your help and hard work! I appreciate it very much!! Thanks again!

Help with formula please.

MrsFrankieH

Active Member

sheetspread

Well-known Member

Excel Facts

MrsFrankieH

Active Member

MrsFrankieH

Active Member

sheetspread

Well-known Member

sheetspread

Well-known Member

Eric W

MrExcel MVP

MrsFrankieH

Active Member

MrsFrankieH

Active Member

Similar threads

Forum statistics

Share this page

Help with formula please.

Active Member

Well-known Member

Excel Facts

Active Member

Active Member

Well-known Member

Well-known Member

MrExcel MVP

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock