If statement inside vlookup? I don't know

[AdRock]

I have 2 spreadsheets.

The first spreasheet (spreadsheet 1) in column A, is a list of post code areas (NW, SO, IP etc). There are 3 other columns each with different numbers (costs) for that postcode area.

In the other spreadsheet (spreadsheet 2) I have a list of orders. Each order has a postcode area and a quantity and a cost (which i need to calculate).

What I need to do is look up each of the postcode areas and the quantities in spreadsheet 2 and possible do a vlookup (or whatever i need) on spreadsheet 1 so i can find the cost.

I know i can do a vlookup to find the postcode area and return a value but how i choose the right cost for the given quantity?

example: it should look for PL in spreadsheet 1 and find 20 as the cost and for the other order it should find 25

Spreadsheet 2 order

P/Code Qty Cost
PL 1 ?
PL 2 ?

Spreadsheet 1

P/Code 1 2 3
PL 20 25 30

I haven't got a clue how i choose which column to look in so any help appreciated.

I hoe i have explained it well enough

 

[jberwald]

What is your determining factor in choosing which of the 3 columns of cost is chosen for a postcode?

 

[AdRock]

What is your determining factor in choosing which of the 3 columns of cost is chosen for a postcode?

The QTY field

If it is 1 it looks up 1 in the other spreadsheet and gets the cost

 

[justanotheruser]

I'm not entirely sure, but I think that if the quantity is 1 item, then it should charge 20 from looking at the spreadsheet 1 layout, if 2 items, 25, 3 items, 30 etc?

 

[AdRock]

I'm not entirely sure, but I think that if the quantity is 1 item, then it should charge 20 from looking at the spreadsheet 1 layout, if 2 items, 25, 3 items, 30 etc?

Yes... that is exaclty right.

I need to lookup the postcode and get a cost for the qty, so if its quantity is 1 it returns 20

 

[Charlie45]

Sheet1
Your lookup Table is in A1:D10


Sheet2
PL in A1
1 In B1

Your formula in C1 would be =VLOOKUP(A1,Sheet1!A1:D10,INDIRECT("B1")+1,FALSE)

 

[AdRock]

Sheet1
Your lookup Table is in A1:D10


Sheet2
PL in A1
1 In B1

Your formula in C1 would be =VLOOKUP(A1,Sheet1!A1:D10,INDIRECT("B1")+1,FALSE)

Many thanks

That works sport on.

Quick question though....where there is INDIRECT("B1"), how do i get that to change when i drag the formula down to other rows? Or do i need to manually change the cell reference?

 

[Charlie45]

That cell reference will have to be changed manually.