Excel lookup formula

Sparxy

New Member
Joined
Mar 17, 2009
Messages
25
Hi there,

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

Excelspreadsheet.jpg


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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 
Upvote 0

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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
 
Upvote 0

Sparxy

New Member
Joined
Mar 17, 2009
Messages
25
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
 
Upvote 0

Forum statistics

Threads
1,191,369
Messages
5,986,259
Members
440,015
Latest member
knijgh

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