CODEWITHSUNDEEP
google-sheetsformulaarray-formulas
Eric
Eric

Reputation: 13

Formula to check if one cell is within a range between two cells

I'm trying to search through two columns with a given value. For example:

A(values)
0-2
3-4
5-6
7-8
9-10

B
275
285
295
305
330

now say I have 3 as a given value. I would like to compare it with the range of values in A so in a logical sense it would fall under 3-4 and return 285.
I think Vlookup would take part ... maybe an if statement.

Upvotes: 1

Views: 1603

Answers (3)

pnuts
pnuts

Reputation: 59495

It may be simpler to change your A values and use a formula like: 

=vlookup(D1,A:B,2)

enter image description here

In which case any value greater than 9 would also return 330 (unless say an IF clause precludes that).

vlookup without a fourth parameter makes inexact matches (as well as exact) and when the first column of the lookup range is sorted ascending will chose the match appropriate to the highest value that is less than the search_key.

Upvotes: 1

JPV
JPV

Reputation: 27292

In addition, if you use 'closed ranges' you can try something like:

=ArrayFormula(VLOOKUP("3", {REGEXEXTRACT(A2:A6, "(\d+)-"), B2:B6}, 2, 1))

Upvotes: 0

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

=LOOKUP(3,ARRAYFORMULA(VALUE(LEFT(FILTER(A:A,LEN(A:A)),SEARCH("-",FILTER(A:A,LEN(A:A)))-1))),FILTER(B:B,LEN(B:B)))

Upvotes: 0

Related Questions