Pick a value which falls between a range of values

[questforexcel]

Hello People,

I have two questions:

1) I would like my formula to pick a value which may fall within a specific range of data.
For eg. a specific data point : 026

to be picked from the following data sets of:
005-075
077-090
091-100

I would like my vlookup function or any other formula to pick "026" from the range of "005-050" as it falls in this bracket. How do I do that?

2) I have 3 -4 columns of these data ranges (005-075, 077-090 etc) and corresponding cost values.
How do place a vlookup formula for a search item such that, if the data is not available in the first column, it should reference from the next set of columns and match their cost values.

Meaning I want it to search such that Vlookup(026,"range set",1,0) if not in 1 then vlookup(026,"range set",2,0), if not in 2, then vlookup(026,"range set",3,0).

Could you please help me out with these.

Thank you

 

[GR00007]

If I understand you, you can use IFERROR to determine when a value is not found in a lookup:
=IFERROR(VLOOKUP(value,range1,offset,false),IFERROR(VLOOKUP(value,range2,offset,false),IFERROR(VLOOKUP(value,range3,offset,false),"Not Found")))

 

[questforexcel]

That would help solve the second bit.

Thank you so much for your quick response.

Would you know on how to make excel pick a value between a range of values as in for eg. how to pick 6, from the range 1-10 and then take its corresponding cost value.

Thank you

If I understand you, you can use IFERROR to determine when a value is not found in a lookup:
=IFERROR(VLOOKUP(value,range1,offset,false),IFERROR(VLOOKUP(value,range2,offset,false),IFERROR(VLOOKUP(value,range3,offset,false),"Not Found")))

 

[GR00007]

If you change what you are looking up, possibly a 'Cost' sheet with values 1 thru 10 each with the desired cost; you could even combine the other tables in a similar manner and have one lookup to the cost tab that would include all the values and their associated cost.

 

[questforexcel]

Thank you for your help