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,

 

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

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