?
Solved

Excel VLOOKUP

Posted on 2003-03-13
14
Medium Priority
?
1,402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Technology Partners: 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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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