# Thread: VLOOKUP problem (Excel 2002)

1. ## 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

2. ## 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. ## 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

4. ## 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. ## 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. ## 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.

8. ## 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.