mardi 6 janvier 2015

Excel, IF statement, vlookup - too many parameters

Can you please help me to simplify or guide me into right direction. My function is suppose to first check if the 7 characters in a word exist in LABEL table, if not, then check 6 first, then 5, etc... This is because the table can contain CHARLYL and CHARLY. When I put below the formula in, I get an error "Too many parameters". I didn't realise there is a limit in IF statements... is there a simplified, alternative way of doing the same?



=if(isna(VLOOKUP(LEFT(A2,7),LABEL!A:B,2,0)),
if(isna(VLOOKUP(LEFT(A2,6),LABEL!A:B,2,0)),
if(isna(VLOOKUP(LEFT(A2,5),LABEL!A:B,2,0)),
if(isna(VLOOKUP(LEFT(A2,4),LABEL!A:B,2,0)),
if(isna(VLOOKUP(LEFT(A2,3),LABEL!A:B,2,0)),
"MISC",VLOOKUP(LEFT(A2,3),LABEL!A:B,2,0)),
"MISC",VLOOKUP(LEFT(A2,4),LABEL!A:B,2,0)),
"MISC",VLOOKUP(LEFT(A2,5),LABEL!A:B,2,0)),
"MISC",VLOOKUP(LEFT(A2,6),LABEL!A:B,2,0)),
"MISC",VLOOKUP(LEFT(A2,7),LABEL!A:B,2,0))

Aucun commentaire:

Enregistrer un commentaire