1

what the formula may be used to find the "Label" with the corresponding "Value" bigger than "Inquiry"

enter image description here

1
1

Delete everything from Column E, including the header in E1. Then place the following formula in E1:

=ArrayFormula({"Header Text";IF(D2:D="",,IF(D2:D>MAX(B2:B),"Out Of Range",IF(D2:D<B2,A2,VLOOKUP(IFERROR(VLOOKUP(D2:D,{B2:B,ROW(A2:A)},2,TRUE),9^9)+IF(ISERROR(VLOOKUP(D2:D,B2:B,1,FALSE)),1,0),{ROW(A2:A),A2:A},2,FALSE))))})

You may replace "Header Text" inside the formula with whatever you want as your real header.

After that, here is a brief description of what happens:

• If any cell in D2:D is blank, the corresponding cell in E2:E will be left blank.

• If any value in D2:D exceeds your MAX value in B2:B or is not a numeric value, the formula will return "Out of Range."

Otherwise...

• The inner VLOOKUP returns the row number of the value that is equal to or smaller than the target value.

• The outer VLOOKUP looks up that row number plus 0 (if the previous VLOOKUP return result was exact) or plus 1 (if the previous VLOOKUP return result was not exact) and returns the Column-A value for the that row number.

2
  • Perfect! Thank you, Eric!
    – S1ARHE1
    Aug 3 at 14:46
  • You're welcome.
    – Erik Tyler
    Aug 4 at 3:46
0

Just to round things out...

Nothing like the error-checking and arrayformula of @ErikTyler but possible solutions all the same. Maybe useful for someone in the future.

QUERY
=query(A2:B6,"select A where B >"&D2&" limit 1")

  • use limit 1 to return a single value

INDIRECT/ADDRESS/MATCH
=(indirect(address(match(D2,$B$1:$B$6,1)+1,1,4)))

  • MATCH with search type = "1" ... "finds the largest value less than or equal to search_key when range is sorted in ascending order", and then we "+1" (add one) to get the next highest value.
  • ADDRESS takes the row number from MATCH, Column = 1 (Column A) to return value, such as A5
  • INDIRECT return the value of the address.

IFERROR/VLOOKUP/INDIRECT/MATCH
= iferror(vlookup(D2,B:B,1,0),indirect("A"&match(D2,B:B)+1))

  • IFERROR allows a result if VLOOKUP fails
  • VLOOKUP = standard lookup
  • INDIRECT/MATCH is a variation of a theme of the INDIRECT/ADDRESS/MATCH used above.

EXAMPLES

All the formulas were entered in row#2, and then copied down to row#5.

snapshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.