Formula grouping number pairs

[Brew]

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

 

[Lewiy]

Try this:

=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]

Or possibly:

=IF(F1>4,F1-4,F1+1)&IF(G1>4,G1-4,G1+1)&IF(H1>4,H1-4,H1+1)

 

[Brew]

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]

=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:

=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]

Great thanks ExcelChampion that works for case 1, but how do I modify that formula for case 2 and case 3

 

[ExcelChampion]

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?

If not, follow this example:

Book1
	C	D	E	F	G	H
1
2	0	1		5	6	9
3	1	2
4	2	3		541
5	3	4
6	4	5
7	5	5
8	6	4
9	7	3
10	8	2
11	9	1
Sheet1

Formula:

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

 

[Brew]

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]

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

 

[ExcelChampion]

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.