Need Iteration formula by index and match function

Rais Ahmed

New Member
Joined
Oct 8, 2016
Messages
7
Hi Every one,

I had one very good and almost accurate excel formula for calculating a specific value by index and match function. I lost my external hard drive and that formula too. Can body body help me to do that formula again as I almost not working on excel over two year and above. What I need to calculate value between to 2 number based on another value. As you can see below H is Height and A is some value relative to specific value in column H. If I want to calculate value of A based on H = 4 what should be formula. Thank you for your time reading this.
H
A
3
0.83
50.88
101
15
1.03
201.06
301.09
401.12
501.14
601.15
801.18
1001.2
1201.22

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
Probably not much use to you, but your figures reasonably closely fit the following natural logarithmic curve ...

= 0.103*LN(A2) + 0.7352

type this into a relevant cell and copy down ... the current formula uses A2 assuming the 'H' value of 3 is in cell A2.

I'm not sure what you were meaning when you said you used to use Index/Match.

Kind regards,

Chris
 

Rais Ahmed

New Member
Joined
Oct 8, 2016
Messages
7
Dear Chris,

First of all Thank you very much for reply and try to understand my complex formula. Your formula will not work as I need iteration formula which I had before using index and match function. Now I will explain you in details, the values in Column H and A is predetermined values which we cannot change. I need value between the two values within range value from column H. Suppose I need to find value of 12 what should be value from column A,? within range value of 10 and 15 from column H.

12
Value from below range = ?
H
A
3
0.83
5
0.88
10
1
15
1.03
20
1.06
30
1.09
40
1.12
50
1.14
60
1.15
80
1.18
100
1.2
120
1.22

<tbody>
</tbody>

Thanks!
Rais

Probably not much use to you, but your figures reasonably closely fit the following natural logarithmic curve ...

= 0.103*LN(A2) + 0.7352

type this into a relevant cell and copy down ... the current formula uses A2 assuming the 'H' value of 3 is in cell A2.

I'm not sure what you were meaning when you said you used to use Index/Match.

Kind regards,

Chris
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
So you don't know yourself what A will be when H = 4. If so, this is not an Excel formula question.
 

Rais Ahmed

New Member
Joined
Oct 8, 2016
Messages
7

ADVERTISEMENT

So you don't know yourself what A will be when H = 4. If so, this is not an Excel formula question.

A will be value between 0.83 to 0.88. I can simply take this value and divided by 2 to get value for 4. But as you go down interval between two values is very large and this divided by 2 method will not work to get exact value.

Regards,
Rais
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Are you expecting linear interpolation? For example if lookup value is 12 that 2/5ths of the way between 10 and 15 so result should be 2/5ths of the distance between 1 and 1.03, i.e. 1.012

For that result with "H values" in A3:A14 and "A values" in B3:B14 try this formula

=LOOKUP(A1,A3:A13,B3:B13+(B4:B14-B3:B13)*(A1-A3:A13)/(A4:A14-A3:A13))
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Ok,

this formula should work for what I think you're after ...

=OFFSET(A1,MATCH(D2,A2:A13,1),1)+((D2-OFFSET(A1,MATCH(D2,A2:A13,1),0))/(OFFSET(A1,MATCH(D2,A2:A13,1)+1,0)-OFFSET(A1,MATCH(D2,A2:A13,1),0))*(OFFSET(A1,MATCH(D2,A2:A13,1)+1,1)-OFFSET(A1,MATCH(D2,A2:A13,1),1)))

this assumes ...

* your table exists in range A2:A13 ... adjust formula as needed
* the H value to be researched is in D2 ... adjust formula as needed

this formula returns an A value of 0.877988 when H is 4 for example

the way it works is ... I'll use an H value of 12 as an example ...

* it finds that 12 exists between the provided H values of 10 and 15
* it finds that 12 is two-fifths of the way through that range of 10 and 15
* it finds the corresponding A values (for H values of 10 and 15) are 1 and 1.03
* it finds the value that is two-fifths of the way through that range of 1 and 1.03 .... this equates to 0.012
* it then adds the 0.012 to the lower of the two A values in the range investigates ... in this case 1

so the final answer is that when H = 12, A = 1.012

I think this is what you're asking for

Kind regards,

Chris
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
wow, just saw that barry had responded to your job while i was entering mine

looks like we're doing the same thing, buy barry's is far shorter and therefore more elegant than mine

Kind regards,

Chris
 

Rais Ahmed

New Member
Joined
Oct 8, 2016
Messages
7
Hi Barry & Chris,

Mr. Barry, Your formula work perfect man!!!!!!!!!!!! It gives exact values. I am really appreciating your effort to help me, this is what exactly I want. Thank you very much guys.

Mr. Chris, I didn't check your formula yet and taken your advise to check Mr. Barry's one. However I will check it later on and let you know if it will work also.

Thank you very much both of you!!!!!!!!!!

Regards,
Rais
 

Rais Ahmed

New Member
Joined
Oct 8, 2016
Messages
7
Hi Chris,

I checked your formula it work perfectly fine like Mr. Barry's formula. Thanks for your support and valuable time!!!
The only difference is that as you mentioned below (this formula returns an A value of 0.877988 when H is 4 for example) returned 0.8550 as in my case correct. However formula work for me great and thanks again!

Regards,
Rais


Ok,

this formula should work for what I think you're after ...

=OFFSET(A1,MATCH(D2,A2:A13,1),1)+((D2-OFFSET(A1,MATCH(D2,A2:A13,1),0))/(OFFSET(A1,MATCH(D2,A2:A13,1)+1,0)-OFFSET(A1,MATCH(D2,A2:A13,1),0))*(OFFSET(A1,MATCH(D2,A2:A13,1)+1,1)-OFFSET(A1,MATCH(D2,A2:A13,1),1)))

this assumes ...

* your table exists in range A2:A13 ... adjust formula as needed
* the H value to be researched is in D2 ... adjust formula as needed

this formula returns an A value of 0.877988 when H is 4 for example

the way it works is ... I'll use an H value of 12 as an example ...

* it finds that 12 exists between the provided H values of 10 and 15
* it finds that 12 is two-fifths of the way through that range of 10 and 15
* it finds the corresponding A values (for H values of 10 and 15) are 1 and 1.03
* it finds the value that is two-fifths of the way through that range of 1 and 1.03 .... this equates to 0.012
* it then adds the 0.012 to the lower of the two A values in the range investigates ... in this case 1

so the final answer is that when H = 12, A = 1.012

I think this is what you're asking for

Kind regards,

Chris
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top