I have a VLOOKUP and corresponding IF statements that give the correct responses. Here is a portion of the VLOOKUP which is on worksheet "Tally" and Tally!$G$35 is the cell that contains "Monaghan". 1......2........3......4......5 VC Monaghan VC Hours 50 SL Monaghan SL Hours 6 JD Monaghan JD hours 10 PD Monaghan PD Hours 21 DS Monaghan DS Hours 18 EP Monaghan EP Hours 14 ER Monaghan ER Hours 36 (Monaghan is listed in the actual VLOOKUP only once, after VC. I needed to write the name each time to keep the rows and columns correct. I also added the "." for the same reason.) The IF statement is"=if(BB11="",""),if(c11=tally!$G$35,VLOOKUP(BB11,Tally!$F$35:$V$47,5,0).... Cell C11 contains "Monaghan" and BB11 contains a drop down menu; if "VC" is selected, the correct number appears in the appropriate cell. I have just been informed that EP and ER need to broken out into separate sums rather the totals that were previously used. EP is now divided into "snow', "heat" and "park" and ER is now divided into the same three categories. It seems as if I could change the VLOOKUP by replacing ER with "snow","heat", and "park" and do the same with EP. The problem I am having is with the drop down menu and the IF statement. The drop down menu can only consist of VC, SL, JD, PD, DS, ER, EP, and a blank. How can I rewrite the IF statement so the subdivision totals for ER and EP will be appear in the appropriate cell. Thank you.

Why can't the drop down contain ER (Heat), EP (Heat), ER (Snow), EP (Snow), etc.? Won't you need to have rows for these values in your LOOKUP table? If not, where will you be storing the data realted to the 3 (6) new sub-categories?

DerbyDad03, I realize that I would need to add additional values in the LOOKUP table but that isn't my major problem. I can't use your suggestion of adding ER (Heat), EP (Heat), etc, to the drop down as only ER and EP, without any additions, are allowed. Without those qualifiers, I don't know how to show ER Heat, etc, values in the appropriate cells. Thank you.

re: only ER and EP, without any additions, are allowed. Who says? You're building the spreadsheet aren't you? Add the rows for EP (Heat) etc. to your lookup table and add EP (Heat) etc. to your drop down. If, for whatever reason, you really can't do that, you haven't given us enough information for us to modify any formulae. We don't know where the Heat, Snow or Park data is stored and we don't know how you are choosing Heat, Snow or Park. Generically, I tried the following and it seemed to work. Only you can determine how to incorporate this method into your spreadsheet. Let's say I want to use a VLOOKUP but I don't know which col_index_num to pull data from because of my second criteria (Heat, Snow, Park). Assuming that the data for Heat, Snow and Park are on the same row as what I am looking up, I can use MATCH to select the VLOOKUP col_index_num. Let's say ER or EP is in A5 Let's say Heat, Snow or Park is in B5 Let's say this data table is in A1:G2: A B C D E F G 1 EP Heat 10 Snow 11 Park 12 2 ER Heat 14 Snow 15 Park 16 This formula will LOOKUP EP or ER from A5 and then set the col_index_num by using MATCH to find the relative position of the value in B5 and then adding 1 to it. =VLOOKUP(A5,A1:G2,MATCH(B5,A1:G1,0)+1,0) For example, if A5 contains ER and B5 contains Snow, the formula will be equivalent to: =VLOOKUP("ER",A1:G2,5,0) and will return 15 Note: The MATCH function only uses 1 row for it's lookup_array, so the position of Heat, Snow and park has to be the same in every row of the Lookup Table. If it's not, then things get very complicated.

DerbyDad03, Thank you for your response. I'll try to incorporate MATCH into my design; if that is unsuccesful, I'll try to convince the end user, my son-in-law, to allow an extended drop down menu which will include the ER and EP subsets. Brian W