Sunday, November 11, 2012

yogi_Extract Values From A Table By Matching A Specified Number With Minimum Value In A Column With Different Number Of Decimal Points

                                          Google Spreadsheet   Post  #866
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Nov 11, 2012
user Luis_Santos said : (!category-topic/docs/spreadsheets/qxY1Pu37au4)
Index-Match an infinite number

I'm trying to Index-match some data, but when i try to match an infinite value in a column (like for example, the result of 1.39/3) it says it didn't find the value.

The formula works great when i don't want to match an infinite number.

Is there a way around this?

Thank you in advance
This is what my sheet looks like:
The values in the "Price" column are gotten from another sheet and the values in the "Price/Hit" column are calculated from the values in "Price".
The actual value you can see in that image, on the "Best Value" row is the minimum value of "Price/Hit" and i want to Index-Match that value in the column. And as you can see, it's an infinite number, hence the error "Did not find value 0.416666666666667".
The index-match formula i'm using is the folowing: =index(Price,Match(E30,PricePerHit)) (being E30 the 0.4167 value in that example).
following is a solution base don my best understanding of what the user is trying to accomplish