# Excel lookup formula

#### Sparxy

##### New Member
Hi there,

Below is an image of a sample of a spreadsheet i am trying to make:

In this spreadsheet, i want to be able to enter the site & house type in C19 & C20 and have the data from the "Master sheet" (above in this case) copied into the correct boxes below, it needs to be able to recognise different house types on the same site and be able to recognise lots of different sites.

1. Is this even possible? I have tried and cant for the life of me work it out.

2. If so, how?

3. Also if this does work as i want it to, is there a way to make cells C19 & C20 into drop down boxes so the data can just be selected.

I know that is alot of questions and question 3 isnt really important its just something i was wondering.

Any help will be very much appreciated.

Thanks,

Sparx

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hello sparxy,

If the A to I values are always in the same order try this formula in C22 copied down

=LOOKUP(2,1/((C\$19=C\$3:E\$3)*(C\$20=C\$4:E\$4)),C7:E7)

Hi there,

Here is what I would do.

In cell C5 enter the formula =c3&c2
this will give you a unique number/value
Copy formula to right

Then click in C19
Go to Data - Validation
In the Allow field choose List
In the source field shade your new formula range (C5:E5 in the pictured example
Ensure In Cell Dropdown is checked off.

Then in cell C22, I would enter the following formula
=hlookup(\$C\$19,\$C\$5:\$E\$15,3,false)

and in the cells C23 - C30, you can drag the formula.
You then need to manually change the cells to,4, ,5, ,6, etc for the balance

There may be an easier way, but I believe this captures everything you wanted.

Hope this helps

Hi guys,

Thanks for the swift response.

Barry Houdini your response solved that problem much more easily that the long formula i was trying. Works perfect now so thank you very much.

Thanks for the response aswell Braindiesel.

Cheers guys.

Sparx

Replies
5
Views
622
Replies
3
Views
775
Replies
0
Views
291
Replies
2
Views
291
Replies
1
Views
382

Threads
1,221,153
Messages
6,158,237
Members
451,477
Latest member
CWebbers

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