Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
[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. |
#4
|
|||
|
|||
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. Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup problem - unable to get the vlookup property | Excel Programming | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) |