Excel Master Required: How do I make a Lookup equation look for 3 items???

Question

I'm trying to get a promotion at work and working out a solution to this intese Excel problem will make me into a hero here and help me learn about Excel.

Here is the problem:

I have a massive excel file 5000 rows long and 15 columns wide.

Column A has the name of the incentive Contract
Column B has the names of Car Models
Column C has the incentive type (either lease or retail)
Column D has the length of term (either 24, 36 or 48)
Column E has the Interest rate %.

I need a lookup I can put in a cell that finds the percentage interest rate based on the Incentive contract, specific Model, Incentive Type and Length of Term

For example: "Ford Gold Medal Lease" "Ford Focus" "Lease" "36"

I need the % interest rate in Column E to be the result of the equation. Please help me out, I need it...

Answer 1

I have an approach that should solve your problem. I would suggest you start by printing out this answer, and working with a copy of the database until you are sure it is working.

1. Insert a new col. A. Now your former cols. A through E will be B through F.

2. Let's insert a couple of rows at the top of the database where you can put the lookup criteria and formula.

2. Let's assume the first row of data is 5. In A5 enter the following formula:

=B5&C5&D5&E5

This will "concatenate" the first 4 cols. of your data, i.e it will make a text string like "Ford Gold Medal LeaseFord FocusLease36"

Copy and paste A5 down A through the last row of your data.

3. Select A5 through Fn, where n = the last row of your data, and name the range "Table1" The easiest way to name it after the range is selected is to click on the box to the left of the formula bar, type Table1, and press Enter.

4. Now A1 through D1 will be set aside for you to enter the 4 lookup critera (Ford Gold Medal Lease, Ford Focus, etc.)

5. In E1 enter the following formula:

=VLOOKUP(A1&B1&C1&D1,
Table1,6,FALSE)

The formula is split on two lines to display properly here.

Format E1 as a percentage.

E1 will now display the interest rate, based on what you have input in A1 through D1. If E1 displays #N/A it means that no match was found. Note that to find a match, A1 through D1 must exactly match cols. B through E on some row of your database.

I hope this makes you a hero!

Answer 2

ideally, you ought to get a minimum of 6 hours of sleep each night, on a similar time as getting 8 or perhaps 9 is plenty maximum advantageous... yet that the "minimum" quantity of sleep needed relies upon on what form of projects you desire to accomplish in the process the day, and how your physique works. Distinguishing the minimum quantity of sleep needed isn't precise, as this truly relies upon on the guy. Getting merely 4 hours of sleep for an prolonged quantity of time could, in spite of if, truly placed on down your device, greater advantageous to ailment, prevalent fatigue and different wellbeing issues..

Answer 3

Have you tried autofilter. They filter out what you want and you see the interest of what you selected.