VLookup Function Trouble
Posted by N. Shockley on January 19, 2001 11:50 AM
I need help with using the VLOOKUP function. Maybe I'm using it improperly, maybe not, but I am not getting the results I want.
Here's my spreadsheet:
I've got a lookup table, range AG12 to AJ121. It's made up of 4 columns - Region Code, Customer, Volume, and Net Price. It's sorted by Region Code and then by Customer Name. I will be updating the data every month, and have built a section into my spreadsheet that will hopefully be grabbing the data in my lookup table by REGION, and putting it in the appropriate region section.
For example, one of the region code values is "MA". I want to pull out all MA region code data from my lookup table and put it in the MA section. So in cell C185, I've got "MA". In cells D185, E185 and F185, I've got the vlookup functions, hoping to return all data rows from my look up table with region code of MA, thus returning customer, volume, and net price, respectively. I will be doing the same for all other regions, for example, "MW", "NE", and "EX", in similar sections of spreadsheet.
Here's my formula for Customer, in cell D185:
VLOOKUP($C$185,$AG12:$AJ$122,2,FALSE)
Here's my formula for Volume, in cell E185:
VLOOKUP($C$185,$AG12:$AJ$122,3,FALSE)
My formula for Net Price is same as above, except 4,FALSE.
Okay, I copied my formulas down, of course. It returns the first customer for MA region correctly, and I have no problem with getting the correct volume and net price values for each customer. The second line
down returns the same value as above. Then the third line down and on returns different customers correctly. For example:
1) A & P Tea 200 .0211
2) A & P Tea 200 .0211
3) A. M. Briggs 400 .0503
4) Albertson's 300 .0450 and so on....
The problem here is, my first returned row was repeated incorrectly.
The next problem I have is bigger. I've set up a second section where I want to do a return from my lookup table on all data with region code values of "MW". So in cell I185, I've got "MW". Then in cell J185, K185, and L185, I've got my vlookup functions written exactly the same way as the ones listed above. Now I want all customers from my lookup table returned here, with a region code value of "MW". What I'm getting is this:
1) All Seasons 200 .0403
(Correct, this customer is the first customer in my lookup table with a region code of MW, which in my lookup table is on line 31.)
2) All Seasons 200 .0403 then keeps repeating on down my list.
This sums up my problems. I would appreciate any help you can give. I really need to get this function to work correctly for me, it will save me a lot of work in the future (cutting and pasting gets old.)
Thanks, N. Shockley
Posted by Hal Turchin on January 19, 2001 12:30 PM
I believe your problem is in your Table. The "lookup" column must be sorted in Ascending order in order for the VLOOKUP to function correctly. If not, you will get unexpected results.
Posted by Loren on January 19, 2001 1:31 PM
Not if you use the False setting.
Posted by Aladin Akyurek on January 19, 2001 7:07 PM
ASSUMING THAT YOUR LOOKUP TABLE SORTED AND THE MA REGION OCCUPIES THE FIRST ROWS AND THE WAY YOU REFERENCE THIS TABLE IN YOUR VLOOKUP-FORMULA, I WOULD EXPECT A DUPLICATE MA ROW.
THIS IS TO BE EXPECTED. IN THE NEW SECTION YOU START REFERRING TO YOUR TABLE IN THE SAME WAY AS YOU DID FOR THE MA REGION. THAT IS, $AG12:$AJ$122. WITH A NEW REGION DOWN YOUR TABLE YOU NEED TO REPLACE $AG12 BY THE CELL REFERENCE WHERE THE NEW REGION STARTS.
CONCLUSION: YOU SHOULD HAVE NO DUPLICATES IN YOUR LOOKUP TABLE AND WHEN REFERRING TO THE LOOKUP TABLE TO GET THE VALUES ASSOCIATED WITH A REGION YOU SHOULD CHANGE $AG12 IN $AG12:$AJ$121 TO THE CELL WHERE THE VALUES ASSOCIATED WITH THAT REGION STARTS.
JUST CURIOUS: WHY DO YOU NEED THIS FORMULA-BASED "COPY-AND-PASTE" THING? ARE YOU COMPUTING THINGS ON SECTION THAT CANNOT BE DONE ON THE ORIGINAL TABLE?
Aladin
Posted by Nora on January 23, 2001 11:31 AM
My data is sorted in ascending order, by region. I am using the False setting, but what does that mean? What is the "True" setting?
Posted by Nora S. on January 23, 2001 11:38 AM
My lookup table is sorted, and has no duplicates. I figured out that it would work if I changed the cell references to exactly where the lookup item starts, but then what is the point of a lookup table? My data will change each month, so how am I saving time and energy with a lookup table if I must go in and change the formulas' cell references for 8 different lookup value sections? Maybe I misinterpretted what a lookup table is supposed to be used for. Do you understand what I mean? Excel must have a function to do what I'm trying to...
Posted by Aladin Akyurek on January 23, 2001 11:47 PM
Nora: FALSE (or 0 for that matter) means that an exact match is required between the lookup-value and a value in the first column of the lookup table for vlookup to proceed. TRUE (or 1) makes vlookup work, as it were, with "approximate" matches. Whenever the latter is intended, you can omit the 4th arg of vlookup in the formulas. In your case, the appropriate "setting" would be FALSE (or 0).
Aladin
Posted by Aladin Akyurek on January 24, 2001 2:19 PM
[ snip ]
Hi Nora,
Since you want your data related to a region copied into a section of its own, you might try the schema below. This is worked out for the MA region. Don't worry: it is applicable for all regions.
First, name the range AG12:AJ121 CUSTOMERS and the range AG12:AG121 REGIONS via Name Box.
Enter the region code 'MA' in C184 , the labels Customer Name, Volume, and Net Price in D184 thru F184.
Here are the formulas and/or constants that are needed.
In C185: =COUNTIF(REGIONS,"MA"), name C185 nMA via the Name Box;
In C186: the number 185, call this cell CurRow via the Name Box.
In D185: =IF(ROW()-ROW($D$184) LessThanEqualTo nMA,OFFSET(INDEX(CUSTOMERS,MATCH(regMA,REGIONS,0),2),ROW()-CurRow,),"")
In E185: the same formula as the one in D185 where you change the number 2 to 3;
In F185: the same formula as the one in D185 where you change the number 2 to 4.
Copy down the last three formulas as far as needed.
Go to the range where you want the data of e.g., MW. Adjust the ranges in the above set of formulas & constants where appropriate. And so on.
Aladin
