VLookup + IF

[reidynem]

I am looking for some help with a VLookup + IF statements. I basicall
want to return a price from a table based on the values input from
columns.
The setup table looks like the following:

Post Code Service Quarter Half 1 2

HR Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
SY7 Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
SY8 Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
WR Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00

and the table I want to input into would be:

Post code Service No of Pallets Price

HR Premium 1

I basically want the price column above to return the correct value fro
the setup table based on the lists selected in post code, service & n
of pallets.

Any ideas on how I do this

 

[Claus Busch]

Hi,

Am Tue, 18 Feb 2014 16:26:04 +0000 schrieb reidynem:

HR Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
SY7 Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
SY8 Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00
WR Premium £28.00 £30.50 £33.00 £64.00
Economy £26.50 £28.00 £29.50 £58.00

and the table I want to input into would be:

Post code Service No of Pallets Price

HR Premium 1

you have to insert the post code in all rows. And you have to delete the
leading and trailing spaces in your values.
Then you can try it in sheet2 with:

=INDEX(Sheet1!A1:F100,MATCH(A2&B2,Sheet1!A1:A100&Sheet1!B1:B100,0),MATCH(C2,Sheet1!A1:F1,0))
and enter the array formula with CRTL+Shift+ Enter.

Or look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "PriceList"


Regards
Claus B.

 

[reidynem]

Thanks for the help Claus....I had a look at your price list sheet an
moved my tables around and deleted columns etc to match yours and then
pasted your formula.

It is returning a #N/A value. What can I do to correct please? Your
seems to work but I couldn't view the formula on your worksheet.

Hope what I am saying makes sense but I can email you print screens i
that helps?

Thanks again.

 

[Claus Busch]

Hi,

Am Tue, 18 Feb 2014 18:06:11 +0000 schrieb reidynem:

It is returning a #N/A value. What can I do to correct please? Yours
seems to work but I couldn't view the formula on your worksheet.

you can right click and download the workbook.
I guess the error comes because you have leading and trailing spaces in
your values. Select a whole column and run TextToColumns => Fixed width
=> Finish. That will delete the spaces.

Hope what I am saying makes sense but I can email you print screens if
that helps?

Screenshots are not helpful in your case. But you can post me the
workbook.


Regards
Claus B.

 

[reidynem]

Hi,

Am Tue, 18 Feb 2014 18:06:11 +0000 schrieb reidynem:
-

you can right click and download the workbook.
I guess the error comes because you have leading and trailing spaces in
your values. Select a whole column and run TextToColumns => Fixed width
=> Finish. That will delete the spaces.
-

Screenshots are not helpful in your case. But you can post me the
workbook.


Regards
Claus B.

Hi Claus,

Thanks so much for your help - it now works great thanks to you. Smal
question though, I made the post code field a list - dependant o
another list i.e. I have a column called zones and if zone A fo
instance is selected then the post code column only calls up the pos
codes in that zone. This works fine and your formula still works bu
when the cells were previously blank, your formula returned a £0.00
Now, when they are blank it returns a #N/A. Is there a quick fix fo
this? No worries if not, I will have to live with it!

Thanks again

 

[Claus Busch]

Hi,

Am Wed, 19 Feb 2014 11:57:58 +0000 schrieb reidynem:

This works fine and your formula still works but
when the cells were previously blank, your formula returned a £0.00.
Now, when they are blank it returns a #N/A. Is there a quick fix for
this? No worries if not, I will have to live with it!

try:
IFERROR(yourformula,"")


Regards
Claus B.