# Multiple lookups

#### Dwaine T.

##### New Member
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
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

Replies
2
Views
495
Replies
8
Views
561
Replies
9
Views
173
Replies
6
Views
245
Replies
0
Views
522

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.

### Which adblocker are you using?

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

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