Multiple lookups

Dwaine T.

New Member
Joined
Oct 15, 2002
Messages
1
I want to be able to automate a seemingly simple task involving square footage, age of a structure, and be able to add in a surcharge or not. Is it possible to write a formula whereby a user would enter data as follows:
for area, a number such as 101;
to add or not add a surcharge (a "Y" or "N" entry);
an age falling within a group of ages ranges (17 falls within 16-20), one of several ranges;

Finally, the result would be indicated in a static cell, but the numbers would be in reverse order from the actual answer.
(The result of 5495, taken from a row indicating areas from 100-125 and a column indicating age of 16-20, plus a $30 surcharge taken from a surcharge column (if Y was entered) yields 5255 (5495 + 30= 5525, the reverse of which is 5255))
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
Dwaine,

Here are rough steps of a possible solution to the first part of your problem:

1. Set up your x,y table of answers (let x be area and y be building age) so that each column and row label is the maxium for that category. Ie, the x and y labels for the example you gave would be 125 and 20.

2. Use VLOOKUP and HLOOKUP to find which column or row your user's data falls in. Ie, =VLOOKUP(user_input,label_range,0,TRUE). If your user entered 17 for age, this would return the next-highest value among the y-labels, 20. If your labels change as regular multiples, you could use ROUND, CEILING, or other techniques here, but the lookups ensure you always get back a legitimate label.

3. Use MATCH to convert those results into an x and y offsets from the northwest corner of the table by MATCHing the labels returned in (2) to the range of all x or y labels.

4. Use INDEX to find the cell at the x,y intersection indicated by the results in (4).

5. Add the surcharge value times =IF("yes",1,0) - just a binary switch.

I'm sorry, I can't think how to reverse the numbers of the answer. Please let us know if you need more specific examples of how the above might work.

Hope this helps,
Alex
This message was edited by Alriemer on 2002-10-16 23:35
This message was edited by Alriemer on 2002-10-16 23:37
 

Forum statistics

Threads
1,148,332
Messages
5,746,139
Members
423,994
Latest member
blzxatly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top