Too many arguments for IF nested loops
Posted by gra on May 26, 2001 2:15 PM
B2 c2
TOWN AREA
ISLE OF DOGS b5 WEST END f5 EDMONTON k5 (AREAS)
POP b6 COV f6 BPK k6 (TOWN NAMES)
ADK b7 EUS f7 CLA k7 "
HAC b8 SOH f8 CED k8 "
UPK b9 HAM f9 EDM k9 "
CNW b10 BLO F10 FIN k10 "
MED b11 KEN f11 MUS K11 "
LOR F12 NWS K12 "
MAI F13 NFN k13 "
MAR F14 PGN k14 "
MAY f15 STA k15 "
PRI f16 TOT k16 "
NPN f17 "
My prob is: if I enter a 3alpha shortcode town-
name in B2 what formular must I input to C2 to make the respective area ie Isle of Dogs,West End or Edmonton, appear in C2. There are too many arguments(towns) for the IF function and I'm well and truly stuck. If anyone can help I'd be very much obliged.
Posted by Aladin Akyurek on May 26, 2001 2:35 PM
Gra
If you just have 3 areas, an IF formula will do.
In C2 enter: =IF(ISNUMBER(MATCH(B2,B5:B20,0)),B5,IF(ISNUMBER(MATCH(B2,F5:F20,0)),F5,IF(ISNUMBER(MATCH(B2,K5:K20,0)),K5,"not found")))
I've just assumed that B5:B20, F5:F20, and K5:K20 as the ranges where you have the town codes. Adjust them to your situation.
If you have many areas, a different organization of the data may be better.
Aladin
Posted by Sean on May 26, 2001 2:49 PM
===========
Gra,
The best solution for this scenario will be to use lookup tables
create (preferably in a named location on another sheet or file) a database containing your short codes and long names.
eg (for ease I'm putting them on the same sheet)
In column M2-M100 (as many as necessary) place your short names.
In column N2-N100 place the corresponding Long names.
Then enter in B2 your short name
In cell C2 enter the formula:
=vlookup(B2,M2:N100,2,False)
This will return the exact match for your formula.
Excel help form this formula is excellent, if you get stuck.
The reason I suggest putting the database on another sheet is that inserting or deleting rows can cause you problems, therefore you are safer to move it elsewhere.
I hope I have understood your problem correctly, If not get back to me with more clarification.
Sean.
Posted by Sean on May 26, 2001 3:10 PM
=======
Having looked at the problem a little more I have not given you exactly what you want.
In column M enter your short code, In column N enter the Area in which it resides, this will then be returned in cell c2.
Hope this helps.
Sean.
Posted by gra on May 26, 2001 4:02 PM
Posted by gra on May 27, 2001 1:42 AM
Gents, thank you so much for replying so quickly to my plea for help. I can't think of any other medium where help is given so freely and honestly. My spreadsheet is up and running, (after scratching my head for 3 weeks!!) and my problems are over. Thank you again. regards Gra.
Posted by gredis on June 03, 2001 11:50 PM
Try using a series of IF functions, the true argument
can bethe first town name, the false argument can
be the cell reference for the next IF function.
The true argument can be the second town name etc.
This method means that you can have many options
that link the If fuctions together.
It also helps reduce problems with typo errors.
