Finding a Value from a range

**Kevin Rush** · 09-25-2018, 10:54 AM

I'm trying to find a range from a list.

eg.

cell F20 gives me a figure of 5.532

From that figure I would like to determine which section of a storage tank (course) a support should be fitted. the following are the parameters.

Course from TO

5 8.81 11.063

4 6.38 8.81

3 3.95 6.38

2 1.92 3.95

1 0.75 1.92

So with that in mind I would like a return (In cell F21) of 3.

It should be noted that the parameters are subject to change.

the cells are as follows: Course is in C9, From is in P9 & To is in Q9

I hope I've explained that well enough

TIA

**Sam Capricci** · 09-25-2018, 11:05 AM

try this...
=INDEX(C9:C13,MATCH(A1,Q9:Q13,-1))
just focuses on the upper values in col Q, assumes your value of 5.532 is in cell A1 and it will return 3 if I'm understand your requirements.

**Special-K** · 09-25-2018, 11:15 AM

@Sambo Kid: I think column Q needs to be in ascending order for the -1 to work.

**Sam Capricci** · 09-25-2018, 11:21 AM

Special-K, it worked for me when I put the data into col Q in descending order. I used the locations given and the results requested and tried it with 6.5 and it returned 4.

**Special-K** · 09-25-2018, 11:25 AM

Thats weird, typed in into a new sheet and it worked. Didnt work on a previous sheet.

**MrShorty** · 09-25-2018, 11:25 AM

@Special-K. Review the help file for the MATCH() function. 3rd argument -1 requires that the data be sorted in descending order. Not to be confused with the (default) option when the 3rd argument is +1, which requires the data to be sorted in ascending order.

**Kevin Rush** · 09-25-2018, 12:30 PM

Sambo Kid, Thank you!! that worked. Much Appreciated!

**Sam Capricci** · 09-25-2018, 12:31 PM

You're welcome, glad I could help.

Finding a Value from a range

LinkBack

Thread Tools

Rate This Thread

Display

Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Re: Finding a Value from a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Help with finding min. value of range based on another range

Copy the range of series by finding/ref of range

Finding range of Cells containing low (range) values....

Finding the minimum in a range while checking for conditions to exclude cells in the range

Finding the MAX and MIN values in range within a range

Finding specific numbers in a range, with the range being set by a seperate cell

Finding Named Range Name Based on a cell found within that range.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions