Multiple Variables vlookup

sidmark

New Member
Joined
Nov 21, 2012
Messages
7
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 code4YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
KT1185205245260295315325370400425
KT10185205245260295315325370400425
KT12185205245260295315325370400425

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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,623
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
You could setup tables for each type of waste along with a list of waste types like
+Fluff.xlsm
ABCDEFGHIJKLMN
1Post code4YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD EnclosedWaste Types
2KT1185205245260295315325370400425General
3KT10185215245260295315325370400425Business
4KT12185205245260295315325370400425
5
6
7
8
9Post code4YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
10KT1185180300260295315325370400425
11KT10185180350260295315325370400425
12KT12185190325260295315325370400425
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
ABC
1Cost
2Waste typeBusiness
3PostcodeKT12325
4Skip8YD
Sheet2
Cell Formulas
RangeFormula
C3C3=INDEX(INDIRECT(B2&"["&B4&"]"),MATCH(B3,INDIRECT(B2&"[Post code]"),0))
Cells with Data Validation
CellAllowCriteria
B2List=Waste_Types
B3List=Sheet1!$A$2:$A$4
B4List=Sheet1!$B$1:$K$1
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,392
Office Version
  1. 2016
Platform
  1. Windows
Hi SidMark,

Here a worksheet tab called Reference

SidMark.xlsx
ABCDEFGHIJK
1Post code4YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
2KT1185205245260295315325370400425
3KT10185205245260295315325370400425
4KT12185205245260295315325370400425
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
CD
3Start of Customer Post CodeKT12
4Type of Skip16YD
5Price to Customer is £ 400
Quote
Cell Formulas
RangeFormula
D5D5=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
CellAllowCriteria
D3List=OFFSET(Reference!$A$2,,,COUNTIF(Reference!$A$2:$A$999,"> "))
D4List=OFFSET(Reference!$B$1,,,,COUNTIF(Reference!$B$1:$Z$1,"> "))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,623
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback
 

sidmark

New Member
Joined
Nov 21, 2012
Messages
7

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,623
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, you will need to start a new thread.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,723
Messages
5,626,501
Members
416,187
Latest member
L_D18

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