VLookup + IF
 

I am looking for some help with a VLookup + IF statements. I basically want to return a price from a table based on the values input from 3 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 from the setup table based on the lists selected in post code, service & no of pallets.

Any ideas on how I do this?

VLookup + IF
 

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&S heet1!B1:B100,0),MATCH(C2,Sheet1!A1:F1,0))
and enter the array formula with CRTL+Shift+ Enter.

Or look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for workbook "PriceList"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[quote=reidynem;1616249]I am looking for some help with a VLookup + IF statements. I basically want to return a price from a table based on the values input from 3 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 from the setup table based on the lists selected in post code, service & no of pallets.

Any ideas on how I do this?

The formula I wrote to do this was

{=INDEX('Master Prices'!A2:I10,MATCH(B5&C5&D5,'Master Prices'!A3:A10&'Master Prices'!B3:B10&No_Pallets,0),'Master Prices'!C3:I10)}

Unfortunately this does not seem to work.

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

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.

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

Thanks again.

VLookup + IF
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks so much for your help - it now works great thanks to you. Small question though, I made the post code field a list - dependant on another list i.e. I have a column called zones and if zone A for instance is selected then the post code column only calls up the post codes in that zone. 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!

Thanks again.

VLookup + IF
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2