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
Does anyone know why it does this? Thanks for your help in advance. JaiTrai
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(She etName!$F$ 2;0;0;COUN TA(SheetNa me!$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
(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
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?
What is the range (address) of the named range?
What does your VLOOKUP formula look like after naming the range?
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
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
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(She et1!$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.
Thanks your help has been very useful and I figured out that the counta was only for alphabets and count was only for numbers.
ASKER
Thanks epaclm I was just on the site getting some tips as well.
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
Have you tried:
=IF($K2 <> "", VLOOKUP($K2,$IA:$IB,2,FALS E), "")
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
=IF($K2 <> "", VLOOKUP($K2,$IA:$IB,2,FALS
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx a lot!