vlookup()

[I Maycotte]

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

[Toppers]

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
>
>

[I Maycotte]

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.

[Alan]

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.

[I Maycotte]

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.

[Dave Peterson]

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