# Formula grouping number pairs

#### Brew

##### Well-known Member
How do I express the following as a formula for range f2:h2:

case 1
if any number is equal to 0 or 5, then 1
if any number is equal to 1 or 6, then 2
if any number is equal to 2 or 7, then 3
if any number is equal to 3 or 8, then 4
if any number is equal to 4 or 9, then 5

example:
f2:h2=3,5,9
result
415

f2:h2=0,2,7
result
133

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Lewiy

##### Well-known Member
Try this:
Code:
``=IF(F1=0,1,CHOOSE(F1,2,3,4,5,1,2,3,4,5))&IF(G1=0,1,CHOOSE(G1,2,3,4,5,1,2,3,4,5))&IF(H1=0,1,CHOOSE(H1,2,3,4,5,1,2,3,4,5))``

#### Lewiy

##### Well-known Member
Or possibly:
Code:
``=IF(F1>4,F1-4,F1+1)&IF(G1>4,G1-4,G1+1)&IF(H1>4,H1-4,H1+1)``

#### Brew

##### Well-known Member
Thanks, how do I modify the second formula in each of the following cases:

case 2
if any number is equal to 0 or 9, then 1
if any number is equal to 1 or 8, then 2
if any number is equal to 2 or 7, then 3
if any number is equal to 3 or 6, then 4
if any number is equal to 4 or 5, then 5

Case 3
if any number is equal to 0 or 1, then 1
if any number is equal to 2 or 3, then 2
if any number is equal to 4 or 5, then 3
if any number is equal to 6 or 7, then 4
if any number is equal to 9 or 9, then 5

#### ExcelChampion

##### Well-known Member
Code:
``=LOOKUP(F2,{0,1;1,2;2,3;3,4;5,1;6,2;7,3;8,4;9,5})&LOOKUP(G2,{0,1;1,2;2,3;3,4;5,1;6,2;7,3;8,4;9,5})&LOOKUP(H2,{0,1;1,2;2,3;3,4;5,1;6,2;7,3;8,4;9,5})``

The above is hardcoded, but you could create a lookup table.

Or, Lewiy's first formula can be simplified:

Code:
``=CHOOSE(F2+1,1,2,3,4,5,1,2,3,4,5)&CHOOSE(G2+1,1,2,3,4,5,1,2,3,4,5)&CHOOSE(H2+1,1,2,3,4,5,1,2,3,4,5)``

#### Brew

##### Well-known Member
Great thanks ExcelChampion that works for case 1, but how do I modify that formula for case 2 and case 3

#### ExcelChampion

##### Well-known Member
I think the easiest thing for you would be to create a lookup table and use a lookup formula.

Are you familiar with the lookup functions?

Book1
CDEFGH
1
201569
312
423541
534
645
755
864
973
1082
1191
Sheet1

Formula:

=LOOKUP(F2,C2:C11,D2:D11)&LOOKUP(G2,C2:C11,D2:D11)&LOOKUP(H2,C2:C11,D2:D11)

#### Brew

##### Well-known Member
Yes, but I think for this requirement, I want to stay away from a lookup, because, I am interfacing with another program and I will set my data columns as input and outputs. the lookup will not be an input or output for my interface.

#### Brew

##### Well-known Member
but maybe if i put the lookup table in sheet 2, I may be ok....I'll have to check the interface

#### ExcelChampion

##### Well-known Member
Case 1:
=CHOOSE(F2+1,1,2,3,4,5,1,2,3,4,5)&CHOOSE(G2+1,1,2,3,4,5,1,2,3,4,5)&CHOOSE(H2+1,1,2,3,4,5,1,2,3,4,5)

Case 2:
=CHOOSE(F2+1,1,2,3,4,5,5,4,3,2,1)&CHOOSE(G2+1,1,2,3,4,5,5,4,3,2,1)&CHOOSE(H2+1,1,2,3,4,5,5,4,3,2,1)

Case 3:
=CHOOSE(F2+1,1,1,2,2,3,3,4,4,5,5)&CHOOSE(G2+1,1,1,2,2,3,3,4,4,5,5)&CHOOSE(H2+1,1,1,2,2,3,3,4,4,5,5)

Re: LOOKUPs - I'm just thinking the tables would be easier to edit than the formulas above. But, you know what you need better than I do.

Replies
17
Views
563
Replies
0
Views
348
Replies
14
Views
404
Replies
1
Views
139
Replies
3
Views
280

1,191,185
Messages
5,985,187
Members
439,946
Latest member
RoyalRife

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back