VLOOKUP and multiple columns

[Dan Belcher]

Here's a question for an assignment for my computer info systems class.

I have a table posted below in cells A4:E13

Credit National National National National
Rating Area Area Area Area
Score 111 112 113 114
300 3.00% 3.00% 3.00% 3.00%
325 3.25% 3.25% 3.25% 3.25%
350 3.50% 3.50% 3.50% 3.50%
475 4.25% 4.50% 4.75% 5.00%
550 4.50% 4.75% 5.00% 5.75%
650 5.50% 5.25% 6.00% 6.50%
750 6.50% 5.75% 7.00% 7.25%

Shipping Rate Per Pound Shipped is $4.00 (4 is placed in cell E16)

B21:G25 for this partial example of the table...
Credit Shipping Total Total
Customer Rating Destination Weight to Discount Shipping
Number Score Area Ship (lbs.) Amount Cost Invoiced
100234 487 111 2,987 ? ?
100543 300 114 15,487 ? ?
etc. etc. etc...

I need to calculate the Discount Amount based upon a customer's credit
rating score and from there calculate the total shipping cost for that
customer. I must do this TWO ways using the VLOOKUP function. I must do
these both where I only have to type the formula once and can just copy it
down for each row of the table that calculates shipping costs. I must
provide two versions of the VLOOKUP formulas: 1) VLOOKUP using a nested IF
to definte the col_ind_num, and 2) VLOOKUP using a calculation to define the
col_ind_num.

I cannot figure out how to properly use the function -without- using nested
IF functions. Any suggestions?

P.S.
The current formula I have is
=VLOOKUP(C24,$A$5:$E$13,IF(D24=$B$6,2,IF(D24=$C$6,3,IF(D24=$D$6,4,IF(D24=$E$6,5)))),TRUE)*E24*$E$16

 

[Dan Belcher]

Okay, nevermind that. I've figured it out. I used
=IF(B24="",0,(VLOOKUP(C24,$A$5:$E$13,(D24-109),TRUE)*E24*$E$16))

Now, I do have an additional problem. I must repeat this question except
using HLOOKUP instead of VLOOKUP. I am open to suggestions on that now.

 

[Max]

... I cannot figure out how to properly use the function
-without- using nested IF functions
The current formula I have is

=VLOOKUP(C24,$A$5:$E$13,IF(D24=$B$6,2,IF(D24=$C$6,3,IF(D24=$D$6,4,IF(D24=$E$
6,5)))),TRUE)*E24*$E$16

One way

Try:

=VLOOKUP(C24,$A$5:$E$13,
MATCH(D24,$A$6:$E$6,0),TRUE)*E24*$E$16

 

[Max]

.. I must repeat this question except
using HLOOKUP instead of VLOOKUP. Try:

=HLOOKUP(D24,$A$6:$E$13,
MATCH(C24,$A$6:$A$13,TRUE),0)*E24*$E$16

 

[Dan Belcher]

Actually, a little caffeine and transposing the table solved my problems.
Got it working fine now. Thanks for the help though!

 

[Biff]

Hi!

The current formula I have is
=VLOOKUP(C24,$A$5:$E$13,IF(D24=$B$6,2,IF(D24=$C$6,3,IF(D24=$D$6,4,IF(D24=$E$6,5)))),TRUE)>*E24*$E$16

I realize this is just an exercise for learning purposes, but no "good"
spreadsheet designer would ever use that formula!

=IF(B24="",0,(VLOOKUP(C24,$A$5:$E$13,(D24-109),TRUE)*E24*$E$16))

While that may work, this is better:

=IF(E24="",0,VLOOKUP(C24,A5:E13,MATCH(D24,A6:E6,0),1)*E24*E16)

You should base your "blank cell" on the last cell that needs to hold data.
If you use your formula and as you enter the data, as soon as you enter the
Customer ID the formula will return #N/A until you enter the credit score
and region.

I must repeat this question except using HLOOKUP

Hlookup works the same way except the criteria references are just the
opposite of Vlookup: You want to lookup the region and match the rate as
opposed to lookup the rate and match the region:

=IF(E24="",0,HLOOKUP(D24,A6:E13,MATCH(C24,A6:A13,1),0)*E24*E16)

Good luck with the IF version of the Hlookup!

Biff

 

[Max]

Your'e welcome, Dan !