# 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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
3
Views
193
Replies
1
Views
281
Replies
7
Views
829
Replies
4
Views
302
Replies
0
Views
509

Threads
1,219,888
Messages
6,150,753
Members
450,983
Latest member
Dustt24

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

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