Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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))

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: VLOOKUP problem (Excel 2002)

    Hi Hans

    Then Chem-Table 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.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: VLOOKUP problem (Excel 2002)

    Please see Steve's post in this thread.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    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.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •