Range Lookup

[vzwtester]

Hello,

I'm trying to figure out how to do this, so I'm asking the experts. I'm wanting to determine if a number falls between a range. For example, I have the following
E6:G6
200 000 000 000 000 200 000 000 000 999 Test

E7:G6
200 100 000 000 000 200 100 000 200 999 Test 2


If I have 200 100 000 000 888 in A1, is there a way to return which range it's in? (Test or Test2)?

[ChemistB]

Which range is it in? Can't tell from your post where one cell ends and another begins. Can you upload an example workbook (Go advanced> Manage Attachments) showing what you have and what you want?

[vzwtester]

Wow,

I'm sorry about that. It does look confusing. Thank you for the quick response by the way.

Book1.xlsx

[ChemistB]

That's better. Based on what you've given, let us know if this formula works on a larger set of data

=LOOKUP(SUBSTITUTE(A1," ","")+0,SUBSTITUTE($E$6:$E$7," ","")+0,$G$6:$G$7)

[vzwtester]

Thanks. Impressive formula. It appears it works but only 90% probably because I've left something out that I didn't think to provide.

Book1.1.xlsx

[ChemistB]

How about this one?

=IF(SUBSTITUTE(A1," ","")+0>LOOKUP(1E+100,SUBSTITUTE($F$2:$F$14," ","")+0),"N/A",LOOKUP(SUBSTITUTE(A1," ","")+0,SUBSTITUTE($E$2:$E$14," ","")+0,$G$2:$G$14))

[vzwtester]

That's crazy fast. I really feel like I'm bothering you now LOL. I explained more in the doc. Book1.2.xlsx

[vzwtester]

It would've helped if I updated it to show you what I was referring to.

Book1.3.xlsx

[ChemistB]

Hmmmm, maybe this

=IF(SUMPRODUCT(--(SUBSTITUTE($E$2:$E$7," ","")+0<=SUBSTITUTE(A1," ","")+0),--(SUBSTITUTE($F$2:$F$7," ","")+0>=SUBSTITUTE(A1," ","")+0))>0, LOOKUP(SUBSTITUTE(A1," ","")+0, SUBSTITUTE($E$2:$E$7," ","")+0,$G$2:$G$7),"N/A")
See attachment

[vzwtester]

Posted too soon on this. I'm killing you I know. I really do appreciate you attempting this. This is a crazy request. Just so close.

Book1.7.xlsx

[ChemistB]

Yes, Column E needs to be sorted from lowest to highest (just select E,F and G and Custom Sort on E)
for the LOOKUP to work
Is it fesible for you to sort or do you need a new formula?

[vzwtester]

Is there a way to ignore the #values?

[vzwtester]

It is highly possible that I'm doing something wrong. I'm sorting, off E, but 200 200 009 100 444 shows as Test 6 when it should show Test.

[vzwtester]

Yep. I think it was me . I'll keep testing it. I really do appreciate your mad skills.

[ChemistB]

Put an IFERROR around the formula
=IFERROR( your formula, "")
will put a blank ("") instead of an error

[vzwtester]

=IFERROR(IF(SUMPRODUCT(--(SUBSTITUTE($D$2:$D$14," ","")+0<=SUBSTITUTE(A2," ","")+0),--(SUBSTITUTE($E$2:$E$14," ","")+0>=SUBSTITUTE(A2," ","")+0))>0, LOOKUP(SUBSTITUTE(A2," ","")+0, SUBSTITUTE($D$2:$D$14," ","")+0,$F$2:$F$14),"N/A","")

Like that?

[ChemistB]

Almost. Still need a parentheses after the last "N/A" before the ","

[vzwtester]

That worked of course. Thanks again! I'll mark this as Solved.

[ChemistB]

Glad I could help.