Excel lookup formula

[Sparxy]

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

 

[barry houdini]

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)

 

[braindiesel]

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

 

[Sparxy]

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