1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel lookup if value not in LOW : HIGH Range

Discussion in 'Business Applications' started by gjrobbins, Nov 10, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. gjrobbins

    gjrobbins Thread Starter

    Joined:
    Jan 25, 2010
    Messages:
    30
    Hello

    I have encountered a problem with what I thought would be a relatively simple vlookup.

    I have attached a sample sheet with the problem I am having and will try to explain.....

    In essence I have one sheet with a column of account numbers (eg 4000, 4001, 4002 etc) and another sheet showing how these are grouped together eg LOW:4000 HIGH: 4009 TITLE 4000 - 4009 Sales

    My current formula does a vlookup for say acount number 4001 and returns the value of "TITLE"

    The problem I have though is if one of the account numbers (4900 for example) is not shown on the LOW:HIGH Sheet then the current vlookup formula returns "TITLE" for the last true reference. I need it to identify that 4900 is not in any of the LOW:HIGH ranges

    Hopefully quite simple to sort but it is causing me now end of headaches..

    If anyone can help I would be very grateful

    Thank you

    Jeremy
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    you need to add the last element of the formula

    for an exact match you need to add false to the end of the formula

    =VLOOKUP(A4,'Chart Range'!A:C,3, false)

     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    how does the low , high bit work ?
     
  4. gjrobbins

    gjrobbins Thread Starter

    Joined:
    Jan 25, 2010
    Messages:
    30
    Hi Etaf

    Thanks for your reply. I am aware of the true / false element but unfortunately I dont think it can be applied in this circumstance.

    The problem is the Low: High columns give the range of accounts included in a group. EG:

    Low:4000 to High 4009

    If I want to lookup what range 4001 is in the current formula goes down the LOW:HIGH columns and finds the next nearest account. This works fine if all the source accounts are represented between the LOW:HIGH

    The problem I have is if the source data is 4010 and this is not represented in the LOW:HIGH account range.
    Low:4000 to High 4009
    Low:4020 to High 4029

    so 4010 is not in the range. I cant get any of the VLookup variaints to work.

    I would like the result to show "NOT IN RANGE" as 4010 is missing from the range

    Does that help..?

    Thanks for looking

    Jeremy
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,422
    First Name:
    Wayne
    Not sure how you are using the high low - can you just have one column with all the account ids in and then the chart title
     
  6. gjrobbins

    gjrobbins Thread Starter

    Joined:
    Jan 25, 2010
    Messages:
    30
    Unfortunatly not, this is the result I am trying to achieve.

    The source data comes from 2 different places so I am trying to match up the account number to the chart title

    It works perfectly if all the account numbers are included in the range that gives the chart title.

    The problem is, when one of the account numbers is not included in the range that gives the chart title.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you provide us with more details? Please list what the cell houses exactly for data. If you can upload a sample file that would be even better. It is going to be hard if you don't provide more details, as we might miss something.
     
  8. gjrobbins

    gjrobbins Thread Starter

    Joined:
    Jan 25, 2010
    Messages:
    30
    Hi Zack

    I uploaded a file for the first post. I think this shows everything.

    I have tried everything I can think of and cant get it to work...

    Thanks

    Jeremy
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    See if this works for you...

    Code:
    =IF(--A4>--INDEX('Chart Range'!$B:$B,MATCH($A4,'Chart Range'!$A:$A,1),1),"NOT IN CHART",INDEX('Chart Range'!$A:$C,MATCH($A4,'Chart Range'!$A:$A,1),3))
    HTH
     
  10. gjrobbins

    gjrobbins Thread Starter

    Joined:
    Jan 25, 2010
    Messages:
    30
    Zack

    Cant thank you enough... I tried index & match and could not get my head around it in this circumstance.

    It works perfectly!

    Really appreciate your help

    Jeremy
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026270

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice