Find another number to the number shown

[bines53]

Hello Friends,

There are 16 numbers in the range, 8 pairs of numbers .
I want to find a number that is close to the number shown, but by a certain setting.
If you see 2 I want to Result 1, If is 9 I want the result 10, if shown 11 I want the result 12 Etc.

Attached file, 16 numbers, with 8 pairs of numbers with a different color font.

Thank you !

 

[NARAYANK991]

Hi David ,

I foresee this becoming another long thread !

Are the numbers you have given realistic or just made-up ? If they are made-up , can you give numbers which are more representative ?

Will the paired number be present anywhere in the list , or will it occur just after the other number , as you have shown it in your worksheet ?

What will decide the relationship between the two numbers ? The example you have given is not conclusive , since you say the numbers have been paired by colour ; does this mean we have to look for numbers by their colour ?

Narayan

 

[SirJB7]

Hi, bines53!

Being NumberList a named range with the pair of numbers, try this:
B3: =INDICE(NumberList;COINCIDIR(A3;NumberList;0)-2*SIGNO(RESIDUO(COINCIDIR(A3;NumberList;0)+1;2))+1) -----> in english: =INDEX(NumberList,MATCH(A3,NumberList,0)-2*SIGN(MOD(MATCH(A3,NumberList,0)+1,2))+1)

Regards!

 

[bines53]

Hi Sir ,

Perfect !

Thank you !

 

[SirJB7]

Hi, bines53!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Would you believe me that I was tempted to do it with OFFSET instead of INDEX/MATCH?

 

[bines53]

Hi Sir ,

Professionals in Excel like you, easy for them to make a change to something better ,
But emotionally difficult to avoid !
Regards!

David