index match excel with negative and positive numbers

[samiitr]

Good day!
Below is my data. I am trying to use index/match function to pick up both positive and negative numbers (0.096 & -0.65) from column C as my lookup value is 0. Then I need to pick up corresponding Column B values (5.5 & 6). So then I can interpolate. Index/match is picking up 0.096 from col C; but it is not picking up -0.65 from col C
It is picking up 5.5 from column B for first value but it is not picking up 6. Below is the syntax I used, please help me in this regards

=INDEX(C5:C15,MATCH(F5,C5:C15,-1)) this returns 0.096
=INDEX(C6:C15,MATCH(F5,C6:C15,1)+1) #N/A
=INDEX(B6:B15,MATCH(F5,C6:C15,-1)) 5.5
=INDEX(B6:B15,MATCH(F5,C6:C15,1)+1) #N/A

lookup value is 0

B C
2.5 93.77
3 28.3
3.5 10.83
4 4.8
4.5 2.25
5 0.94
5.5 0.096
6 -0.65
6.5 -1.25
7 -2.63

[MrShorty]

Assuming F5 contains the value 0 and C5:C15 is the full column of values under C:

=INDEX(C5:C15,MATCH(F5,C5:C15,-1)) this returns 0.096

0 is the lookup value in column C. -1 tells MATCH() that the values in column C are sorted in descending order, so it should find the value in column C that is 0 or just larger than 0 (0.096 in this case) and return the corresponding row number. INDEX() then returns the corresponding value from column C, which 0.096.

=INDEX(C6:C15,MATCH(F5,C6:C15,1)+1) #N/A

0 is the lookup value in column C. 1 tells MATCH() that the values in column C are sorted in ascending order (note that they are not). 0 is smaller than the first value in column C (93.77), so MATCH() returns N/A error. INDEX() simply propagates the error.

=INDEX(B6:B15,MATCH(F5,C6:C15,-1)) 5.5

0 is the lookup value. -1 tells MATCH() that the values are sorted in descending order, so MATCH() finds the value that is just larger than 0 (0.096) and returns the corresponding row number. INDEX() then returns the corresponding value from column B, which is 5.5.

=INDEX(B6:B15,MATCH(F5,C6:C15,1)+1) #N/A

0 is the lookup value. 1 tells MATCH() that the values are sorted in ascending order (they are not). 0 is smaller than the first value in the range, so MATCH() returns the N/A error. INDEX() then propagates the error.

If I understand what you are trying to do, in the N/A cases, I think you want the "match type" argument to be -1 just like it is in the other cases. If I am misunderstanding what you want, perhaps the explanations above will help you understand what the INDEX(...MATCH()) combination is doing and can better formulate what you want to happen.

[WideBoyDixon]

Assuming these values are in B6:C15 then:

Please Login or Register  to view this content.

Returns 5.564343 for me (although that assumes linear change)

WBD

[samiitr]

Thank you for clarity of the syntax

[samiitr]

Assuming these values are in B6:C15 then:

Please Login or Register  to view this content.

Returns 5.564343 for me (although that assumes linear change)

WBD

Thank you. This works well. I am trying to understand first two codes.

[MrShorty]

I overlooked that this was, overall, an interpolation problem. Are you required to use Excel? Gnumeric has a built in INTERPOLATION() function that makes short, easy work of interpolation problems.

For those who must use Excel, I like this approach to linear interpolation type problems: https://www.excelforum.com/excel-gen...ml#post4421017

[WideBoyDixon]

Thank you. This works well. I am trying to understand first two codes.

It's basically looking for the last row that has a value greater than zero. Using LOOKUP like this is pretty standard.

WBD