trying to figure out a two way lookup to determine a value

[wdk]

I have a large spreadsheet that lists numerous customers. Each
custtomer has a code for the service that we provide them and then has
a price negotiated at the time of service. We are have set up a price
list that gives the service code, in a vertical column and then has a
range of dollar values for each service code (about 20 separate codes)
ranging from $0 to $75 broken up in 5 grade (E,D,C,B, A) categories.
The pricing is different ranges for each service code. What I need to
do is have each customer line, take their code, find a match in the
Price LIst code, the take the price they are paying for that code and
see which letter grade range if is closest to, and then put that grade
in a field for that customer. I know how to simple vlookup, but I
don't know how to tell it to do what I think amount to a vlookup and
hlookup (has to return the grade value at in whichever of the 5
columns apply.) I scoured the site and feel it will involve a match
and index at least, but can't figure out how to use them correctly.

 

[Bernard Liengme]

Generally one use a MATCH expression inside an INDEX formula
A Google with the search term: excel two-way lookup
will give you lots of good hints. Then come back if more is needed.
best wishes

 

[ilia]

It's difficult to give you an example without knowing your data
structure. Can you list a portion of your service codes, customer
list, transaction list, or whatever you have?

 

[wdk]

It's difficult to give you an example without knowing your data
structure. Can you list a portion of your service codes, customer
list, transaction list, or whatever you have?



- Show quoted text -

price_list
sample data
code E D C B A
name code price Grade
2-1 0 15 25 50 65
smith 2-1 31.50 ? (should return C)
2-2 0 25 40 65 75
jones 2-1 68.25 ? (should return A)
3-1 0 20 30 55 70
phillips 3-1 18.75 ? (should return E)
3-6 0 40 50 60 75
wilson 2-2 41.50 ? (" C)
In other words, it looks at the customer's code, then price, then
looks at price list and "grades" the price based on the various levels
for that service. My next step after getting them graded, is that for
every grade, another table will choose the percentage of price
increase (I can figure that one out).

 

[wdk]

price_list
sample data
code E D C B A
name code price Grade
2-1 0 15 25 50 65
smith 2-1 31.50 ? (should return C)
2-2 0 25 40 65 75
jones 2-1 68.25 ? (should return A)
3-1 0 20 30 55 70
phillips 3-1 18.75 ? (should return E)
3-6 0 40 50 60 75
wilson 2-2 41.50 ? (" C)
In other words, it looks at the customer's code, then price, then
looks at price list and "grades" the price based on the various levels
for that service. My next step after getting them graded, is that for
every grade, another table will choose the percentage of price
increase (I can figure that one out).- Hide quoted text -

- Show quoted text -

Very sorry- the way I put it in is scrambled in the formatting- I will
try again
code rate Grade CODE E D C B A
2-1 $85.29 ? -should be B 2-1 0 15 36.76 41.83 100
2-3 $65.00 ? Should be C 2-2 0 59.25 64.77 74.91 107.45
2-4 $207.00 ? Should be A 2-3 0 84.49 92.78 107.99 156.79
2-4 $86.00 ? Should be D 2-4 0 109.74 120.79 141.07 206.14
2-5 $66.57 ? Should be E 2-5 0 134.99 148.8 174.14 255.48
2-5 $141.00 ? Should be D 2-6 0 164.71 181.29 212.8 312.16

 

[ilia]

I'm not entirely sure how this will work, because why is $86 for code
2-4 D? Shouldn't it be E?

I put the codes and grades in cells starting on J1. This way, the
code listing is in range J2:J7. The code headers are in range K1:01.
The customer data is starting in A1. Here's the formula, starting in
D2:

=INDEX($K$1:$O$1,1,MATCH(B2,OFFSET($J$1,MATCH(A2,$J$2:$J$7,0),1,1,5),
1))

First, it matches the code in column A, and generates a range (for the
INDEX function) using the OFFSET function. Next, it matches the
lowest amount closest to rate in column B. Finally, it returns the
corresponding grade letter.

However, these are the results I got for your data:

code rate Grade My result
2-1 $85.29 ? -should be B B
2-3 $65.00 ? Should be C E
2-4 $207.00 ? Should be A A
2-4 $86.00 ? Should be D E
2-5 $66.57 ? Should be E E
2-5 $141.00 ? Should be D D

Your example is inconsistent. $85.29 for code 2-1 should be B,
according to you, which is the lower value closest to 85.29. $65 for
code 2-3, then, should be E - because the second highest is 84.49.
Maybe you meant code 2-2, in which case the formula above yields C.
Same problem with $86 for code 2-4; if you meant 2-3, again, the
formula above yields D as you indicated.