1

I am trying to combine Index & Match to pull the value inside of Column B based on if a number is less than or equal to the value inside of Column A on the same row. So for example if I give the formula 0 than I would like it to return 0.75, 15.25 should return 0.80, etc.

The current formula that I came up with sort of does this but it isn't right. If I put in 0 I would get #N/A and if I put in 15.25 than I get 0.75 which isn't correct.

=INDEX(B1:B11,MATCH(A13,A1:A11))

Example Chart

1

The first problem comes from the fact that the MATCH statement will return "#N/A" if it does not find the value. So the INDEXstatement will not know what to do with this, so you have to manage this case. An IFstatement should be good to deal with this.

=IF(A1>A13,0,MATCH(A13,A1:A11))

With the example above, if you have a value that is smaller than the first number of your array, it will return 0. Next you have to plug this into the INDEXstatement.

Next you have to manage the fact that you want to round up and not down. This can also be managed easily with and IFstatement. You can simply compare the number in the A column with the target cell to see if they are the same, if not you increment your row by 1. You'll just have to nest IFstatements within each other with some help from the INDEXstatement.

=IF(INDEX(A1:A11,IF(A1>A13,0,MATCH(A13,A1:A11)))=A13, IF(A1>A13,0,MATCH(A13,A1:A11)), IF(A1>A13,0,MATCH(A13,A1:A11))+1)

So this will basically make it that it now rounds up instead of down.

The last step is to make it so that you get the value of the 2nd column instead of the first one. You had this correct with the INDEX statement, we just need to put them all together.

= INDEX(B1:B11,IF(INDEX(A1:A11,IF(A1>A13,0,MATCH(A13,A1:A11)))=A13, IF(A1>A13,0,MATCH(A13,A1:A11)), IF(A1>A13,0,MATCH(A13,A1:A11))+1) )

And this gives exactly the results you wanted in your question. Sorry for the long formula, but you wanted something more or less complicated.

| improve this answer | |
  • After looking over your formula it appears there is an issue with it and if A13 is set to something like 14 it returns #VALUE!. But I did some tweaking and made the following which works. =INDEX(B1:B11, IF(A1 > A13, 1, IF(INDEX(A1:A11, MATCH(A13,A1:A11)) = A13, MATCH(A13,A1:A11), MATCH(A13,A1:A11)+1))) – 7H3LaughingMan Nov 24 '16 at 19:36
  • That's weird because it works fine with my version of Excel and your exact dataset. Anyways, glad it helped you get your answer. – IronWilliamCash Nov 24 '16 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.