+ Reply to Thread
Results 1 to 11 of 11

lookup in range of numbers

  1. #1
    Registered User
    Join Date
    03-27-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    2007
    Posts
    5

    lookup in range of numbers

    Hi,

    I want to vlookup in a range of numbers, not that hard with vlookup and true argument. But if the ranges are like this than looking up for example 17 the formula will (ofcourse) return b. And I want it to return #N/A


    0 10 a
    11 15 b
    20 30 c

    Thanks very much!

    Rgds
    Steven

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: lookup in range of numbers

    Use FALSE as your argument instead of TRUE.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,210

    Re: lookup in range of numbers

    Try:

    =LOOKUP(2,1/(A1:A3>=17)/(B1:B3<=17),C1:C3)

  4. #4
    Registered User
    Join Date
    03-27-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    2007
    Posts
    5

    Re: lookup in range of numbers

    Hi,

    Thx for your reply's.

    Both wont work, I ll try to explain better:

    If I use false I will get #N/A for every number that is not a start or end of a range so that won't work.

    =LOOKUP(2,1/(A1:A3>=17)/(B1:B3<=17),C1:C3) this formula is focussing on my example with 17. In reality my range list is much much longer and there are gaps in it, I want #N/A for these gaps.

    I don't manage.

    Rgds
    Steven

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,317

    Re: lookup in range of numbers

    Try

    D1=Search value e.g 17

    =INDEX($C$1:$C$3,MATCH(1,($A$1:$A$3<=$D$1)*($B$1:$B$3>=$D$1),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  6. #6
    Registered User
    Join Date
    03-27-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    2007
    Posts
    5

    Re: lookup in range of numbers

    Thx but also not working here.

    lookup ranges i used:
    0 10 a
    11 15 b
    20 30 c
    31 35 d
    40 50 e


    result with this formula {=INDEX($C$1:$C$5,MATCH(1,($A$1:$A$5<=A9)*($B$1:$B$5>=D3),0))}
    1 a
    5 a
    11 a
    17 a
    21 a
    30 a
    31 a
    36 a
    49 a
    50 a


    If anyone knows the solution, would be much appreciated.

    Rgds
    Steven

  7. #7
    Registered User
    Join Date
    03-27-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    2007
    Posts
    5

    Re: lookup in range of numbers

    nevermind, I was wrong, formula is working perfect!!!

    Many thx!
    Steven

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,317

    Re: lookup in range of numbers

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,210

    Re: lookup in range of numbers

    Quote Originally Posted by Stekke76 View Post
    I want #N/A for these gaps.
    Sorry, change to:

    =LOOKUP(2,1/(A1:A3<=17)/(B1:B3>=17),C1:C3)

  10. #10
    Registered User
    Join Date
    03-27-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    2007
    Posts
    5

    Re: lookup in range of numbers

    Hi Phuocam,

    Yep, works perfect :-) Now I have 2 different formulas which do the same :-)

    Thanks a lot!

  11. #11
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,210

    Re: lookup in range of numbers

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF statement between range of numbers with LOOKUP
    By alexpope in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2015, 12:53 AM
  2. [SOLVED] Lookup Funticon For a Range of Numbers?
    By DRuane in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-07-2014, 04:03 PM
  3. Lookup Range Between 2 Numbers & 3rd Value
    By dleiman in forum Excel General
    Replies: 4
    Last Post: 04-29-2010, 12:29 PM
  4. How lookup numbers between range numbers in 1 cell ?
    By termal in forum Excel General
    Replies: 4
    Last Post: 02-06-2010, 06:57 AM
  5. Lookup a range of numbers
    By Dolphinv4 in forum Excel General
    Replies: 4
    Last Post: 05-24-2005, 06:32 AM
  6. Lookup a range of numbers
    By Dolphinv4 in forum Excel General
    Replies: 4
    Last Post: 05-24-2005, 06:31 AM
  7. [SOLVED] Lookup a range of numbers
    By Dolphinv4 in forum Excel General
    Replies: 5
    Last Post: 05-23-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1