Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
To give you a feel for what I'm trying to do: I have 1000+ different rows of commission rates. There are 7 criteria which define each rate (criteria in A-G and rate in H). I have to link each of these rates to corresponding cells in 4 different sheets. However, I do not want to go back and forth between the commisions table and the other 4 sheets. I want to be able to use vlookup or a lookup type function to find the rate. Vlookup requires that the table be in ascending order (otherwise use false). However, I do not think or at least that I am aware of that I can sort the table in ascending fashion for each criteria. There are bound to be descents. Does anyone have any insight as to what I may be able to use? Thanks in advane, --- Isaac Maycotte -- I Maycotte ------------------------------------------------------------------------ I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604 View this thread: http://www.excelforum.com/showthread...hreadid=562873 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
Why not use FALSE to avoid sorting?
Can you a give sample of input/output data so we can better advise you? "I Maycotte" wrote: To give you a feel for what I'm trying to do: I have 1000+ different rows of commission rates. There are 7 criteria which define each rate (criteria in A-G and rate in H). I have to link each of these rates to corresponding cells in 4 different sheets. However, I do not want to go back and forth between the commisions table and the other 4 sheets. I want to be able to use vlookup or a lookup type function to find the rate. Vlookup requires that the table be in ascending order (otherwise use false). However, I do not think or at least that I am aware of that I can sort the table in ascending fashion for each criteria. There are bound to be descents. Does anyone have any insight as to what I may be able to use? Thanks in advane, --- Isaac Maycotte -- I Maycotte ------------------------------------------------------------------------ I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604 View this thread: http://www.excelforum.com/showthread...hreadid=562873 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
I have used false but do not achieve the appropriate results. Here are two sample rows ProdName Type Option Schedule Band Age Range Rate Product1 A 1 Level 10 61-70 1.50% Product2 B 2 Heap 1 0-5 19.50% I apologize for the formatting, I couldn't get it any better. So, If I were linking to another sheet in the same workbook, I'd like to use the in thie fashion: VLOOKUP(ProdName & Type & Option & Schedule & Band & Age & Range, Table, 8, False) I have tried this but get a #N/A. So, I do not know what I am doing wrong or even if vlookup will work in this way. Thanks. -- I Maycotte ------------------------------------------------------------------------ I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604 View this thread: http://www.excelforum.com/showthread...hreadid=562873 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
The problem you are describing seems to suggest that you are looking to use something like a nested multiple vlookup function (which I don't think exists) but I could propose 2 solutions. The first is to use a macro which sorts through your data line by line to find the appropriate match of all 7 of your criteria and return the rate - not complicated and shouldn't take too long to write or run; in the form of a function it would be very flexible. The second is to somehow combine your 7 criteria on each row into a single unique value in a separate cell and then use vlookup using this new value as the search criterion to find the appropriate rate. Concatination of the criteria may suffice but depending on your data may not give unique values. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
Thanks for the reply Alan. I had given some serious thought to the 2nd solution you proposed, I steered away from it since there are far too many combinations. I could easily write the macro, an option I hadn't considered. I was hoping to also not use macros if excel had such capabilities. However, it seems I just might have to write code. Thanks for your suggestions. -- I Maycotte ------------------------------------------------------------------------ I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604 View this thread: http://www.excelforum.com/showthread...hreadid=562873 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup()
If you want exact matches for just two columns (and return a value from a
third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) I Maycotte wrote: I have used false but do not achieve the appropriate results. Here are two sample rows ProdName Type Option Schedule Band Age Range Rate Product1 A 1 Level 10 61-70 1.50% Product2 B 2 Heap 1 0-5 19.50% I apologize for the formatting, I couldn't get it any better. So, If I were linking to another sheet in the same workbook, I'd like to use the in thie fashion: VLOOKUP(ProdName & Type & Option & Schedule & Band & Age & Range, Table, 8, False) I have tried this but get a #N/A. So, I do not know what I am doing wrong or even if vlookup will work in this way. Thanks. -- I Maycotte ------------------------------------------------------------------------ I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604 View this thread: http://www.excelforum.com/showthread...hreadid=562873 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |