Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VLOOKUP

Posted on 2003-03-13
14
Medium Priority
?
1,406 Views
Last Modified: 2008-02-01
I am having a problem using VLOOKUP. I have the lookup based on the value in the adjacent cell which is a validation box. If the box has a value then the VLOOKUP checks for the matching value based on the two column that are linked to another spreadsheet. The value in the other spreadsheet are sorted so the 1st column is in alphabetical order which VLOOKUP needs to give back a correctly found match. The problem is if the VLOOKUP is > than any field that is not populated beyond row 45, the VLOOKUP does not see the new value. I wanted the VLOOKUP to check the entire column so no matter if a new record was added to the second spreadsheet it would show up in the validation box (which it does) and then the VLOOKUP would find the match (which it does), but only through row 45, unless you keep changing the row number in the formula any time you add a row on the main spreadsheet. This is the VLOOKUP formula '=IF($K2 <> "", VLOOKUP($K2,$IA$2:$IB$58,2,TRUE), ""), this is the way it should look '=IF($K2 <> "", VLOOKUP($K2,$IA$2:$IB$7000,2,TRUE), "").
Does anyone know why it does this? Thanks for your help in advance. JaiTrai
0
Comment
Question by:JaiTrai
  • 7
  • 2
  • 2
  • +3
14 Comments
 
LVL 1

Expert Comment

by:iaminit
ID: 8131399
I know this isn't much of an answer, but try naming the range and referring to it that way in the VLOOKUP.
0
 
LVL 4

Expert Comment

by:GoBanana
ID: 8131526
Like iaminit said, create a named range. And the formula for that range would be =SheetName!$F$2:OFFSET(SheetName!$F$2;0;0;COUNTA(SheetName!$F:$F)-1;2)

(Remove "-1" if cell F1 is empty)

Let us say you name this area ToLookUp, then you change $IA$2:$IB$58 in your VLOOKUP formula to ToLookUp.

Matthias
0
 

Author Comment

by:JaiTrai
ID: 8131581
Hi imaninit, I tried that but it only comes back as an empty value. Any other suggestions?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:iaminit
ID: 8131753
Just so I'm clear on what's happening ...
What is the range (address) of the named range?
What does your VLOOKUP formula look like after naming the range?
0
 

Author Comment

by:JaiTrai
ID: 8132072
Hi imaninit, I tried that but it only comes back as an empty value. Any other suggestions?
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 8132281
This site has got all information you need about VLOOKUP and Dynamic ranges

http://www.cpearson.com/excel.htm

VLOOKUP
http://www.cpearson.com/excel/lookups.htm#LeftLookup

Dynamic Ranges
http://www.cpearson.com/excel/excelF.htm#DynamicRanges

Regards Curt
0
 

Author Comment

by:JaiTrai
ID: 8132454
I tried what GoBanana suggested and was able to get up to 113 as the maximum range number, ie '=OFFSET(Sheet1!$IA$2,0,0,COUNTA(Sheet1!$IA$2:$IA$113),2) which I named Tester, then I used the '=VLOOKUP(K2,Tester,2) which pulled the data up to the empty row which was 58. But if I tried to change the maximum row to be anything greater than 113 it does not read the range. Is the maximums range numbers to formulas in excel?

Thanks your help has been very useful and I figured out that the counta was only for alphabets and count was only for numbers.
0
 

Author Comment

by:JaiTrai
ID: 8132458
Thanks epaclm I was just on the site getting some tips as well.
0
 

Author Comment

by:JaiTrai
ID: 8132487
Thanks epaclm I was just on the site getting some tips as well.
0
 

Expert Comment

by:druid9855
ID: 8133207
Hi JaiTrai:  Are you INSERTing rows in the Lookup range or appending them to the end to add new data.  Also, you said they were sorted alpha...are there any duplicates?  (And what kind of entries are they?).  Why did you use TRUE in the Range-Lookup instead of FALSE..is it because your Lookup Value may not be an exact match? I'm not really seeing the problem with your original formula.  It seems to be working for me.  Good luck -Druid
 
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8134331
Have you tried:

=IF($K2 <> "", VLOOKUP($K2,$IA:$IB,2,FALSE), "")

i.e. $IA:$IB to include all of column IA & IB and FALSE so the list does not need to be sorted?

Or you an deine a name as above which is $IA:$IB: and refer to that, whatever you find easier...

Steve
0
 

Author Comment

by:JaiTrai
ID: 8136310
druid9855 and dragon-it man thanks I can't believe this whole time the only real issue was whether the statement was TRUE or FALSE, once I placed the FALSE in the issue went away.

I was using the TRUE condition because I was sorting the original data, and I assumed that the TRUE was used because a match would be positive in whatever the column it was searching. I'm still trying to understand the logic on that one, but correct me if I'm wrong now.

The formula is basically asking you whether you want to find an approximate match and by saying TRUE that is what it returns, and FALSE is saying no I don't want a approximate match I want an exact match. Is that correct?

Yo it's forumns like this that really gives the internet it's power.

Thanx
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 200 total points
ID: 8136462
Using TRUE needs the first column to be in order and it will pick the closest match if there isn't an exact one, FALSE works whethtr it is in order or not and returns only an exact match.

From my experience if there are multiple entries for the same key TRUE returns the last entry and FALSE the first (as long as they are in order).  If they are out of order then TRUE returns the closest match before something higher in the alphabet and FALSE returns N/A.  Think I've got that right... anyawy I nearly always use FALSE unless I am trying to get the nearest value...

Glad to help!

Steve
0
 

Author Comment

by:JaiTrai
ID: 8137370
Thanx a lot!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question