Lookup based on several criteria plus a few extra requirements

[whiteshadoz]

Hi everyone,

Firstly I am new to the forum so please be patient if I forget to provide some required information.
Secondly I only have a working knowledge of excel so forgive in advance my ability to over complicate things

Ok, the task:

My friend owns 2 different car sales companies and has a spreadsheet which he uses for both companies to calculate an upfront cost based upon model, monthly payment and company he is selling it from. He uses the spreadsheet to display all the prices for his staff to print out and have as a reference point should they need it.
He also has pricing cards for the car windows which are currently hand written.

To speed things up for him, I have designed a new pricing banner on excel and would like it to populate the prices based on 3 criteria allowing him to easily select what he requires to print.

Without giving his confidential information away I have replicated the part of the spreadsheet needed but I have changed the actual names etc to random names/values and much smaller prices but the way the data needs to be looked up will be the same. Also, I haven't included the banner but I can pop the formula in where required.

In the example included the cell D20 is where the formula needs to be and will lookup based on 3 criteria.
D17, D18 and D19 are drop down lists which are used to specify what is to be looked up.
D17 is the brand which is set in data validation to CompA and CompB
D18 is the model which is a list of all column A , named as Model
D19 is the prices which are looked up in row 2, named as Prices

Also, if the data in D19 could only show decimal places where needed, such as showing £75 instead of £75.00 but keeping times when it ends .99, that would be very helpful.

The actual spreadsheet has about 300 models by 20 manufacturers so I can't really specify which cells will be used to name the list by as this will change on a regular basis.
I know you can provide better ways to make the lists so please let me know

Also, the spreadsheet with the prices on cannot be edited. The layout of it is what he needs so it can be only used as a reference point. It will be saved on the same PC as a different file to the banner file but I believe it is possible to lookup from one to another. Again, any tips would be appreciated.

Thank you for your help

[FDibbins]

I have 2 solutions for you.

1, add a helper row above you're data and combine (concatenate) row 2 and 3 =B2&B3
then use this in D20, use this...
=INDEX($A$1:$J$14,MATCH($D$18,$A$1:$A$14,0),MATCH($D$19&$D$17,$A$1:$J$1,0))

if row 3 will always be unique, then you dont need to consider the CompA/B. you can then dispense with the helper row and just use this...
=INDEX($A$2:$J$14,MATCH($D$18,$A$2:$A$14,0),MATCH($D$19,$A$2:$J$2,0))

[whiteshadoz]

Hi thank you for your reply

I have tried both solutions:
Solution 1: the actual spreadsheet which the top half of my example comes from cannot be edited (he has it how he likes) so I can't add a helper row to it.
Solution 2: seems to work fine matching the price option however, there is no guarantee that this will be unique as BrandA and BrandB may use the same prices in the future.

Ideally I need to be able to lookup using all 3 pieces of information.
I can add a separate sheet to the banner file if any data needs to be replicated there to help with lookups.

Thanks again for your help. Fingers crossed on a solution