Excel nested IF alternative help

Internet i shop / Built to suit
January 6, 2009 at 08:23:22
Specs: Windows XP, 2gb
I’m looking for some direction. I’m a former educator that taught basic computer applications to high school students. It was a one semester course that had to cover the basics, and I mean basics, of WORD, EXCEL, ACCESS and POWERPOINT, I was also a wrestling coach. I created a spreadsheet to manage, bouts and scoring for a pool style wrestling tournament. It worked well, a bit cumbersome for an experienced excel user, which I am not, I’m sure, but it worked for me.

The basic function I used was the IF..THEN statement and with 6 teams there no issues. But I recently was asked to adapt it to 12 teams and my nested IF’s are not getting it done do to the 7 limitation.

I’ve researched and gooled the net and tried about all the functions that I feel will work, but each seems to come up short, or rather, my abilities to use excel are not developed enough to get it done.

Here’s what I want to do.

I use columns A – M for various data. Column B contains team names and column M contains team scoring (points earned). So Columns B/M are needed to be “tied/related” to each other for team scoring. The teams’ names are in random order in rows 5-16. And are never in an ascending or descending order.

In separate section of the spreadsheet I’m trying to alphabetize the teams and their corresponding scores. So let’s say in column P (P1-12), I list the teams in alpha order and I want their corresponding score to appear in column M (M1-12). I started column M (M1) with IF(P1=B5,M5,IF(P1=B6,M6,IF(P1=B7, M7,IF(…)))) for my scoring. Then I copied that formula to M(2-12) changing the P1 to P(2-12) accordingly. That works for the 1-6 or 7. But not so good for 8-12.

I had successes using CONCATENATE in another section, but CONCATENATE changes the score from a value to text (?). I need the score to remain a value for other computations and CONCATENATE won’t let me do it.

I tried creating a separate table and using VLOOKUP and thought I had solved the problem. Until I realized the VLOOKUP requires tables to be in ascending or descending order and the teams in column B are always in random order.

I’m unable to figure out Boolean multiplication or how to create custom VBA function.

My most recent effort was to use user defined names. I divided my schools into groups of 6 and created 2 sets of IF’s , one for each group. And named each. That was 12 x 2, or 24 defined names and then used something like: =IF(OneToSix,OneToSix,SevenToTwelve). I had limited success with it. It worked for the first 1-6, but returns #VALUE! for the 7-12.

Any direction would be greatly appreciated. Thanks

See More: Excel nested IF alternative help

Report •

January 6, 2009 at 08:39:28
VLOOKUP will work fine - you just need to use the FALSE tag at the end of the vlookup. It'll only look for an exact match, returning #N/A# if it can't find one. I'm not 100% sure of the layout of your sheet, but in its simplest form (imagine you have team name in column A, and score in column B on sheet1, and the vlookup formula goes in sheet2)


Note you can use full columns if required but on Excel 2003 it is hundreds of times quicker to specify fixed ranges (not sure how complicated the rest of your sheet is)

Report •
Related Solutions

Ask Question