Multiple Variables vlookup

[sidmark]

Hi I am a Hobbyist Excel-ler usually I love to mess around with codes and formulas for silly little things (its how we all start!) but I bit off more than I can chew when offering to look at how my company works out prices for customers( long lists on the wall!)
I work in a skip hire business and have up to 15 choices of skip, 5 of hook loader and 5 of grab loader. All have prices are set on certain UK district post codes i.e. (TW1,TW2,SL1,SL2), Types of waste and the product itself. for instance a 6Yd skip in TW1 for general waste is £205. Nice and easy Ive sent quote off-- everyone is happy.
As you can imagine looking through postcodes on a wall for the different variables is time consuming and inefficient was hoping for a way I could type in Customers Pcode and skip type to get the price which is already tabulated.

Post code	4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
KT1	185	205	245	260	295	315	325	370	400	425
KT10	185	205	245	260	295	315	325	370	400	425
KT12	185	205	245	260	295	315	325	370	400	425

Table above is mock up of skip prices table prices are defaulted as mixed waste, there are several differing types of waste that incurr different costs to customer. The table ideally would be hidden or on a differnt page and once info is entered the price would automatically appear in specified cell.

There is probably an easy answer for you pros and Im overthinking it, but my frazzled brain has given up the ghost!


Thanks for looking

 

[Fluff]

Hi & welcome to MrExcel
You could setup tables for each type of waste along with a list of waste types like

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Post code	4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed			Waste Types
2	KT1	185	205	245	260	295	315	325	370	400	425			General
3	KT10	185	215	245	260	295	315	325	370	400	425			Business
4	KT12	185	205	245	260	295	315	325	370	400	425
5
6
7
8
9	Post code	4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
10	KT1	185	180	300	260	295	315	325	370	400	425
11	KT10	185	180	350	260	295	315	325	370	400	425
12	KT12	185	190	325	260	295	315	325	370	400	425
Sheet1

The first Table is called General & the 2nd is called Business
Cells in N2:N3 are a named range called "Waste_Type

Then on anothe sheet you could use

+Fluff.xlsm
	A	B	C
1			Cost
2	Waste type	Business
3	Postcode	KT12	325
4	Skip	8YD
Sheet2

Cell Formulas
Range	Formula
C3	C3	=INDEX(INDIRECT(B2&"["&B4&"]"),MATCH(B3,INDIRECT(B2&"[Post code]"),0))

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Waste_Types
B3	List	=Sheet1!$A$2:$A$4
B4	List	=Sheet1!$B$1:$K$1

 

[Toadstool]

Hi SidMark,

Here a worksheet tab called Reference

SidMark.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Post code	4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
2	KT1	185	205	245	260	295	315	325	370	400	425
3	KT10	185	205	245	260	295	315	325	370	400	425
4	KT12	185	205	245	260	295	315	325	370	400	425
Reference

Here's the Quote tab. Let's avoid typing errors by offering dropdowns of Post Codes and Skip types. The formulae will let you carry on adding Skip types up to column Z and Post Codes (with the appropriate value cells) down to row 999:

SidMark.xlsx
	C	D
3	Start of Customer Post Code	KT12
4	Type of Skip	16YD
5	Price to Customer is	£ 400
Quote

Cell Formulas
Range	Formula
D5	D5	=INDEX(Reference!$B$2:$Z$999,MATCH($D$3,Reference!$A$2:$A$999,0),MATCH($D$4,Reference!$B$1:$Z$1,0))

Cells with Data Validation
Cell	Allow	Criteria
D3	List	=OFFSET(Reference!$A$2,,,COUNTIF(Reference!$A$2:$A$999,"> "))
D4	List	=OFFSET(Reference!$B$1,,,,COUNTIF(Reference!$B$1:$Z$1,"> "))

 

[sidmark]

Thank you both for replying

They worked great!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Toadstool]

Thank you both for replying

They worked great!

You're welcome!

 

[sidmark]

np
I Just had a thought is there a way I could use this on our website?
with everything protected just the input boxes available for change and quote box viewable by customer?

 

[Fluff]

As this is a totally different question, you will need to start a new thread.
Thanks

 

[sidmark]

np thanks

 

[Fluff]