Formula for finding a cell value within another range of cells

[bcholland]

I have value of 26 in Cell A1, for example and I want to find
whether or not that value falls with a range of cells B1:C2 (which is in another sheet) and
if so return the value in D Range.



A1 B C D
26 20 30 100



How would my formula look?

[BadlySpelledBuoy]

I'm presuming you meant C1 rather than C2?
If so, try the below formula.

=IF(AND(A1>=B1,A1<=C1),D1,"")

[bcholland]

Hi thank you for your reply. Actually looking to find the value in a range. So it can be anywhere
from B1 C1 through B3 through C3. for example

Column Column
B C
10 11
12 26
27 30

So in this case 26 would match the range B2:C2

Hope this makes sense.

[XOR LX]

Hi,

You don't say what the result should be if the value appears more than once in that range, in different rows, but perhaps:

=SUMPRODUCT((B1:C3=A1)*D1:D3)

Regards

[BadlySpelledBuoy]

Or, if the ranges are never going to overlap, i.e. in your latest example the cell under 27 will be 31 because it's next after 30, then you could use this approach (see attached).

[calvob]

Ah I guess Im not explaining properly. See if my sample attached helps. Visuals are better. Thanks again for your help.

[FDibbins]

calvob, your IP indicates that you are also bcholland? If so, why the need for a 2nd ID?

A quick observation on your picture -
1st, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Having said that, I was able to look at your pic, and it looks to me that your "numbers" are actually text that just looks like a number (look at the small green triangle in each cell)

How are those cells being populated? you will need to convert them to numbers (there are various ways to do that), otherwise it's like asking - does Orange fall between March and Soccer