Case selection in Excel 2003

[m2work]

I am working on a form that can be used for 15 different branches.'

If I type branch "10", info for branch 10 will display.
If I type branch "20", info for branch 20 will display.

I can do nested "if" statements, but I can only do up to 7 nested
statements, not for 15.

Is there a way to accomplish this? Any help is greatly appreciated.

Thanks,
m2

 

[Sheeloo]

One option:

Suppose in sheet1 you have 1-15 in Col A, Branch Names in ColB, Manager's
name in Col C
Then
to show branch name in B1 of sheet2 you can use
=VLOOKUP(A1, Sheet1!A:C,2,False)
It will show the branch name corresponding to the number you enter in B1
Similary in C1 you can have
=VLOOKUP(A1, Sheet1!A:C,2,False)
to show manager's name....

 

[JLatham]

You could look at the CHOOSE() function for the worksheet, but I think the
numbers are going to be out of range for you.

Typically this is done with a table and a VLOOKUP() function in a cell.

You'd have a table somewhere set up like this:
C D
1 10 "Branch 10 Information"
2 20 "Branch 20 Information"
....
15 150 "Branch 150 Info"

Now you enter the branch you want into a cell, say A1 and somewhere you have
a VLOOKUP() formula like this:
=VLOOKUP($A$1,$C$1:$D$15,2,FALSE)
The table can even be on another sheet, you just have to add the sheet name
to the formula where it references the table's range.

That will return a #N/A error when there's no match (as when A1 is empty),
so you can wrap it in an 'error trap' like this:
=IF(ISNA(=VLOOKUP($A$1,$C$1:$D$15,2,FALSE)),"",=VLOOKUP($A$1,$C$1:$D$15,2,FALSE))
which will either show you the value from column D when a match is found in
column C to the entry in A1 or it will just look empty when there isn't a
match.

Hope this helps some.

 

[Max]

One interp on your set-up
and a way via index/match

Branch data is assumed in Sheet2, cols A to E,
with key col (branch codes) listed in col B
[data per branch is assumed on a std row-wise, single row basis]

In Sheet1 (your form),
you have the branch codes listed in A2 down
Put in B2:
=IF(ISNA(MATCH($A2,Sheet2!$B:$B,0)),"",INDEX(Sheet2!A:A,MATCH($A2,Sheet2!$B:$B,0)))
Copy B2 across by 5 cols to F2, fill down as far as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik

 

[ShaneDevenshire]

Hi,

Suppose you only had a single column of info to return, so your lookup table
was in A1:B15 with the branchs listed in the A column. If you enter the
branch you want to lookup in D1, than the formula would be

=LOOKUP(D1,A1:B15)

This requires that your table, A1:B15 be sorted in ascending order on column
A.

If you want to return data in column C based on a match in column A then

=LOOKUP(D1,A1:A15,C1:C15)
or shorter still:
=LOOKUP(D1,A1:C15)

 

[m2work]

Thanks, it works.

Also, thanks everyone for providing some great insight!

Thanks,
m2