Return a Value in a Table based upon certain criteria

**Staci** · 03-27-2014, 03:03 PM

In cell B5, I want to return a value based on a Production value in cell B4 using a table located in A7:B28.

Basically, I am looking for a formula to return the value of column A8:A28, in cell B5 that corresponds the closest to B4 from column B8:B28, without going above the next value in the chart... but if the number is greater than 60 it will always return 5 or if the number is less than 31.4 it will always return 0.

In the example attached the result I am looking for is 0.75 based upon a Production Value of 35 in cell b4. If I put a value of 31.75 in B4, I would expect a return of 0.25 in cell B5.

So basically if
Greaterthan.JPG

Please see attached worksheet

Thanks in advance for your help!
Staci

**Staci** · 03-27-2014, 08:02 PM

I figured it out...index match using 1, instead of 0...
=IF(ISERROR(IF(B4<=B8,"0",INDEX($A$8:$A$28,MATCH(B4,B8:B28,1)))),"",IF(B4<=B8,"0",INDEX($A$8:$A$28,MATCH(B4,B8:B28,1))))

Return a Value in a Table based upon certain criteria

LinkBack

Thread Tools

Rate This Thread

Display

Return a Value in a Table based upon certain criteria

Re: Return a Value in a Table based upon certain criteria

Thread Information

Users Browsing this Thread

Similar Threads

Need Formula To Return Value From Data Table Based On 3 Selectable Criteria

[SOLVED] Formula to Return Bottom 3 Values From a Table Based on a Criteria

[SOLVED] Need to return value from one table based on 2 different criteria from another

Return values from table based on 2 criteria

[SOLVED] return cell reference in a table based upon given lookup criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions