Results 1 to 8 of 8
Thread: VLOOKUP problem (Excel 2002)

20050304, 12:15 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
VLOOKUP problem (Excel 2002)
Hi
Hans kindly gave me this formula  =IF(B7="","",VLOOKUP(B7,Chem_Table,IF(Market_Secto r="processed_food",12,3),0)).
I now discover that I need more options, I now need if Market_Sector = "Agriculture" and Market_Sector = "PCT" and Market_Sector = "Dairy" and Market_Sector = "Beverage" + the Processd_Food
I did toy the idea of going to the Chem_Table and Increasing or decreasing the prices based on the original ie.
=IF(Market_Sector="processed_food",C3*(1+$N$1)),IF (Market_Sector="Agriculture",C3*(1+O1)),IF(Market_ Sector="Agriculture",C3*(1+P1)) PS I know this formula in it's present form does not work .
N1 O1 and P1 contain a percentage figure
The first option would be nice if possble
Many thanks
BraddyIf you are a fool at forty, you will always be a fool

20050304, 12:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP problem (Excel 2002)
Are the prices for the different market sectors always going to be a specific percentage of a base price? If so, you could use a lookup table listing market sectors and the corresponding percentages. Otherwise, you could put the price for each sector in a different column, and use a lookup table listing market sectors and the corresponding column numbers in the chem_table range.
BTW: for the future, it would be better to post a question like this in the original thread (unless it has become very long), since it is clearly a continuation of the question asked there. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20050304, 13:11 #3
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: VLOOKUP problem (Excel 2002)
Hi Hans
My apolgies for not following the proper procedure for posting.
Firstly the prices are not specificly a percentage of the base price, the base price is in say column 3 in the Chem_Table, the others can be added say column 4 5 6 etc.
The lookup formula needs to be placed in a cell in a sheet called Chemicals which is where it reads the code in B7, whilst the Market_Sector is selected in a sheet called input.
=IF(B7="","",VLOOKUP(B7,Chem_Table,IF(Market_Secto r="processed_food",12,3),0))
Where I am struggling is to get the VLOOKUP to read Input Sheet Market_Sector as you can see their are now five options and then select the correct column according to which of the five is entered in Market_Sector I hope this clear.
Thanks
BraddyIf you are a fool at forty, you will always be a fool

20050304, 13:19 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VLOOKUP problem (Excel 2002)
I am not entirely clear as to your setup and what you need exactly.
Could you post and example workbook, with some "representative" items, what you want the results to be, and perhaps the "logic" for the results?
Steve

20050304, 13:25 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP problem (Excel 2002)
In the formula from your previous thread, the lookup column is either 12 or 3:
<table border=1><td>Market_sector</td><td>Lookup column</td><td>Processed_food</td><td align=right>12</td><td>other</td><td align=right>3</td></table>
This can be expanded to include other market sectors:
<table border=1><td>Market_sector</td><td>Lookup column</td><td>Processed_food</td><td align=right>12</td><td>Agriculture</td><td align=right>11</td><td>PCT</td><td align=right>9</td><td>Dairy</td><td align=right>6</td><td>Beverage</td><td align=right>5</td><td>other</td><td align=right>3</td></table>
(The column numbers are just examples, you will have to fill in the correct values yourself.)
If you put a table like the above into your spreadsheet, you can use it in the formula. Say that you name the table range Sector_Columns.
=IF(B7="","",VLOOKUP(B7,Chem_Table,VLOOKUP(Market_ Sector,Sector_Columns,2,FALSE),FALSE))

20050304, 13:48 #6
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: VLOOKUP problem (Excel 2002)
Hi Hans
Then ChemTable does not contain the Market_Sector as such it contains the Code in B7 of the chemical page and the base price is in Column 3.
Your formula looks for B7 say 100689 from Chem_Table column 3 unless Market_Sector = Processed_Food, I know you are aware of this. But is the only way I can explain myself.
I apologise if I do not understand your explaination.
BraddyIf you are a fool at forty, you will always be a fool

20050304, 13:52 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP problem (Excel 2002)
Please see Steve's post in this thread.

20050304, 14:13 #8
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: VLOOKUP problem (Excel 2002)
Hi Hans
I have worked it out I followed your example to the letter , and now I have it working.
Many Many Thanks to you and anyone esle who replied.
BraddyIf you are a fool at forty, you will always be a fool