Link to home
Start Free TrialLog in
Avatar of JaiTrai
JaiTrai

asked on

Excel VLOOKUP

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
Avatar of iaminit
iaminit

I know this isn't much of an answer, but try naming the range and referring to it that way in the VLOOKUP.
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
Avatar of JaiTrai

ASKER

Hi imaninit, I tried that but it only comes back as an empty value. Any other suggestions?
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?
Avatar of JaiTrai

ASKER

Hi imaninit, I tried that but it only comes back as an empty value. Any other suggestions?
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
Avatar of JaiTrai

ASKER

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.
Avatar of JaiTrai

ASKER

Thanks epaclm I was just on the site getting some tips as well.
Avatar of JaiTrai

ASKER

Thanks epaclm I was just on the site getting some tips as well.
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
 
Avatar of Steve Knight
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
Avatar of JaiTrai

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JaiTrai

ASKER

Thanx a lot!