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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,487
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)

•range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
•If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data in a range or table.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.
•If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.
 

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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,487
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
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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
 
Joined
Jul 25, 2004
Messages
5,458
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
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top