# Returning a value from a range of data

## Excel Help for Returning A Value From A Range Of Data in Automating Data Analysis Tasks

 Forum Topic Post Reply Login

### Returning A Value From A Range Of Data

Rate this:
(3/5 from 1 vote) Hi,I'm currently trying to find a way to return a numeric value from a list of betweens.  For example, the costs relating to transport cost x amount if less than 100kg, y if between 100 and 200, z if between 200 and 300 and so on.  Is there a way to do this in excel, without doing a very lengthy if formula?If not, I'm not sure how to go about the IF formula, as it contains too many values for excel - I have about 20 possible results. Posted by tinagi on 20 Feb 2006
 Replies - Displaying 1 to 1 of 1 Order Replies By: Most Recent | Chronological | Highest Rated Rate this:
(3/5 from 1 vote)
You can return a value from a range of data by defining your ranges and corresponding values in a table and running a VLOOKUP function with the closest match logical value set to TRUE.

The following example shows the defined values in columns E and F, the raw data in column B and the formula to make the match in column C.

Excel Spreadsheet:
 A B C D E F G 1 2 Weight Transport Min Value 3 122 =VLOOKUP(B3,\$E\$3:\$F\$6,2,TRUE) 0 15 4 245 =VLOOKUP(B4,\$E\$3:\$F\$6,2,TRUE) 100 30 5 253 =VLOOKUP(B5,\$E\$3:\$F\$6,2,TRUE) 200 45 6 360 =VLOOKUP(B6,\$E\$3:\$F\$6,2,TRUE) 300 60 7

The formula from cell C3 can be copied down to cells C4 to C6.
Excel Business Forums Administrator
Posted by on
 Displaying page 1 of 1

 Find relevant Excel templates and add-ins for Returning a value from a range of data in the Excel Business Solutions Directory