Limitation of "IF" conditions in formula

[regmgrgsf]

I need to know how to write a formula that can accomadate more than 7 "if" conditions for a single cell formula. Basically, if I state a month is number "1" then I get this input. If I say it is month "2" then I get that responce. I need to be able to go to at least 12 month choices but from what I see you cannot exceed 7 if conditions within a single formula. Can someone help??

 

[PaddyD]

Hi - welcome to the board!

vlookup() is the function you need - check it out in the help files, seardh this board for loads of examples & post back with more info about how your data is set up if you need more...

Paddy

 

[regmgrgsf]

Appreciate prompt reply but i'm not familiar with "vlookup". Here is the formula that i currently have.
IF(C2=1,$Q6,IF(C2=2,$R6,IF(C2=3,$S6,IF(C2=4,$T6,IF(C2=5,$U6....ETC.
LIKE I SAID "C2" IS THE MONTH NUMBER OF A PROJECT, SAY UP TO #18 FOR A 18 MONTH JOB. IF CELL C2 IS "14" THEN I GET THE VALUE FROM $BB6 (EXAMPLE ONLY). CAN YOU TELL ME HOW TO APPLY "VLOOKUP" TO THIS??

 

[PaddyD]

You will need

1) a 'lookup table' that relates your values (month number of project) to the values you want returned (the values in BB6 etc). You will need these to be in contiguous area of the worksheet, not picked up from a cell here, a cell there.

2) a vlookup formula that interrogates this table & returns the apropriate matched values. Vlookup formulas have the form:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

2) to indicate what bit of the help file you didn't understand? which example did you look at from previous posts to the board?

Paddy

 

[Aladin Akyurek]

On 2002-09-26 20:23, regmgrgsf wrote:
Appreciate prompt reply but i'm not familiar with "vlookup". Here is the formula that i currently have.
IF(C2=1,$Q6,IF(C2=2,$R6,IF(C2=3,$S6,IF(C2=4,$T6,IF(C2=5,$U6....ETC.
LIKE I SAID "C2" IS THE MONTH NUMBER OF A PROJECT, SAY UP TO #18 FOR A 18 MONTH JOB. IF CELL C2 IS "14" THEN I GET THE VALUE FROM $BB6 (EXAMPLE ONLY). CAN YOU TELL ME HOW TO APPLY "VLOOKUP" TO THIS??

=INDEX(Q6:BB6,C2)

 

[PaddyD]

I really must read what the OP's are saying! Not even lack of coffee as an excuse!

Paddy

P.S. very nice 'sum every x cells' formula - Juan should be proud of the dedication

 

[Larry_S]

I seem to recall that INDEX takes the row number and then the column number as its second and third arguments.

So maybe try =Index(Q6:BB6,1,C2)

 

[Aladin Akyurek]

On 2002-09-26 22:55, Larry_S wrote:
I seem to recall that INDEX takes the row number and then the column number as its second and third arguments.

So maybe try =Index(Q6:BB6,1,C2)

Compare the shorter version with the longer version...