Need help for user worksheet - lookup array?

[pom1331]

I'm IT support for our Branch, and I'm trying to create a worksheet for one of my users in Purchasing.
He needs to look up a part, and figure out what DC Code that part is. To do this, he looks up the part and gets four prices (List, Fleet, Dealer, Dist.) He then needs to run three checks (% difference between List/Fleet, % diff. List/Dealer, % diff. List/Distributor). He then takes those three % differences, and cross references them with a chart to find out what DC code he needs.

I've created a spreadsheet that will allow him to type in the four costs, and it will automatically figure out the three % differences. I want to try and create a formula that will take these differences, and find the DC for him without him having to do it manually. Some of the criteria are the same.

Here is the lookup chart, so that you can see what it looks like.

Check 1 Check 2 Check 3 DC Code
0% 0% 0% 39
3% 18% 30% 45
5% 16% 24% 29
5% 21% 29% 30
5% 25% 35% 42
5% 26% 33% 31
5% 26% 33% 46
10% 16% 24% 41
10% 31% 38% 36
10% 31% 38% 44
10% 31% 39% 32
10% 31% 39% 37
10% 31% 39% 38
10% 31% 41% 40
30% 30% 30% 35

I've tried many different things, but while I'm a decent everyday user of Excel, I'm not skilled enough to figure this out.

I've tried using concatenation, INDEX and MATCH formulas, but I don't understand them well enough to rework the examples I've found in order to get them to work.

IS it a problem that the three values I'm getting and trying to use to look up the information are coming from formulas, and are not actual, static values?

Any help out there?

Thanks for even taking a look.

Chad

[Corticus]

have you tried vlookup?

[pom1331]

I've tried the lookup wizard, I wasn't too sure of VLOOKUP to try it on it's own. I had tried both VLOOKUP and the lookup wizard on another user's spreadsheet, and eventually we got the wizard to work.

I haven't found an example I felt comfortable enough in trying to rework, as most I have found don't deal with three different criteria that need to be matched.

I can do the easy formula's and know my way around Excel, but the heavy duty formula's are still beyond me. I'm learning though, I'm completely self taught on Excel.

Can you point me to a VLOOKUP example I might be able to reverse engineer and attempt to get it to work?

I really appreciate anything you can show me.

Thanks!
Chad

[Scott R]

What DC Code are you supposed to return when the 3 %'s are 10%, 31%, 38%? It looks like it could be 36 or 44.

[pom1331]

One of the many problems I've run into with this. The nearest thing we can tell is that he would have to lookup the description field of the DC codes (which I've left off of the chart above for space constraints) and figure out which one it would be.

Make sense?

Thanks!
Chad

[Chris Davison]

On 2002-05-14 08:32, pom1331 wrote:
I'm IT support for our Branch, and I'm trying to create a worksheet for one of my users in Purchasing.
He needs to look up a part, and figure out what DC Code that part is. To do this, he looks up the part and gets four prices (List, Fleet, Dealer, Dist.) He then needs to run three checks (% difference between List/Fleet, % diff. List/Dealer, % diff. List/Distributor). He then takes those three % differences, and cross references them with a chart to find out what DC code he needs.

I've created a spreadsheet that will allow him to type in the four costs, and it will automatically figure out the three % differences. I want to try and create a formula that will take these differences, and find the DC for him without him having to do it manually. Some of the criteria are the same.

Here is the lookup chart, so that you can see what it looks like.

Check 1 Check 2 Check 3 DC Code
0% 0% 0% 39
3% 18% 30% 45
5% 16% 24% 29
5% 21% 29% 30
5% 25% 35% 42
5% 26% 33% 31
5% 26% 33% 46
10% 16% 24% 41
10% 31% 38% 36
10% 31% 38% 44
10% 31% 39% 32
10% 31% 39% 37
10% 31% 39% 38
10% 31% 41% 40
30% 30% 30% 35

I've tried many different things, but while I'm a decent everyday user of Excel, I'm not skilled enough to figure this out.

I've tried using concatenation, INDEX and MATCH formulas, but I don't understand them well enough to rework the examples I've found in order to get them to work.

IS it a problem that the three values I'm getting and trying to use to look up the information are coming from formulas, and are not actual, static values?

Any help out there?

Thanks for even taking a look.

Chad

Hi Chad,

the "description field" needs to be clarified in so far as if it is a determining factor, then you need to build that in with your 3 %ages somehow so a =VLOOKUP can differentiate.

Let's assume for the minute that there is no duplication of %ages... this should get you started :

If it were me, I'd "cheat" by inserting a column before the DC code which simply concatenates (glues together) the three %ages

So if your %ages are in A1, B1 and C1 respectively, in your new column D1, try :

=A1&B1&C1

this should give you weird-looking values like :

3.0018.0030.00 etc

You can then access the values of the formulae you've already provided which give him %age answers in a VLOOKUP :

(lets say they were sitting in J5,K5 and L5)

=VLOOKUP(J5&K5&L5,$D$1:$E$15,2,0)

as I say, this will bring back a DC code, but you need to factor in that description somehow for any repeat %ages

does this help ? come back if you feel it's making progress (or not also, of course!)





_________________
Hope this helps,
Chris
(Excel '97, Windows ME)

[ This Message was edited by: Chris Davison on 2002-05-14 14:34 ]

[pom1331]

Got it! I now know how to use the VLOOKUP and ROUND formulas too!

Of course, I'm going to keep working on it to try and really make it easy for them by making it go ahead and pull up the correct pricing matrix and fill it out for them, but that's for another bottle of aspirin!

Chris, your suggestion was dead on, and saved me a lot of headaches. I owe you a pint buddy. If you're ever in New Orleans, look me up. Or if you ever have a computer problem, email me, I might be able to help!


Thanks again to everyone else too - you are all life savers!